Better way to Partially UNPIVOT in Pairs in SQL

Question

I have the following table

VendorID    EMP1A       EMP2A       EMP1B       EMP2B
----------- ----------- ----------- ----------- -----------
1           4           3           5           4
2           4           1           5           5
3           4           3           5           4
4           4           2           5           5
5           5           1           5           5

I want to UNPIVOT in pairs so I get the "A" columns on one row and the "B" columns on another

vendorid    employee  orders      employee  orders
----------- --------- ----------- --------- -----------
1           EMP1A     4           EMP2A     3
1           EMP1B     5           EMP2B     4
2           EMP1A     4           EMP2A     1
2           EMP1B     5           EMP2B     5
3           EMP1A     4           EMP2A     3
3           EMP1B     5           EMP2B     4
4           EMP1A     4           EMP2A     2
4           EMP1B     5           EMP2B     5
5           EMP1A     5           EMP2A     1
5           EMP1B     5           EMP2B     5

This works but it seems like I'm working too hard

DECLARE @pvt AS TABLE( 
  VendorID INT, 
  EMP1A    INT, 
  EMP2A    INT, 
  EMP1B    INT, 
  EMP2B    INT); 

INSERT INTO @pvt VALUES (1,4,3,5,4),
(2,4,1,5,5),
(3,4,3,5,4),
(4,4,2,5,5),
(5,5,1,5,5)

;WITH piv1 
     AS (SELECT vendorid, 
                employee, 
                orders 
         FROM   (SELECT vendorid, 
                        EMP1A, 
                        EMP1B 
                 FROM   @pvt) p 
                 UNPIVOT (orders FOR employee IN (EMP1A, EMP1B) ) 
                AS 
                unpvt), 
     piv2 
     AS (SELECT vendorid, 
                employee, 
                orders 
         FROM   (SELECT vendorid, 
                        EMP2A, 
                        EMP2B 
                 FROM   @pvt) p UNPIVOT (orders FOR employee IN (EMP2A, EMP2B) ) 
                AS 
                unpvt) 
SELECT piv1.vendorid, 
       piv1.employee, 
       piv1.orders, 
       piv2.employee, 
       piv2.orders 
FROM   piv1 
       INNER JOIN piv2 
         ON piv1.vendorid = piv2.vendorid 
            AND RIGHT(piv1.employee, 1) = RIGHT(piv2.employee, 1) 
WHERE
    piv1.orders > piv2.orders

Note: This is a simplified example and there's actually 25 pairs that need to be converted to rows and I also want easily be able to filter

e.g. adding WHERE piv1.orders = piv2.orders produces

vendorid    employee  orders      employee  orders
----------- --------- ----------- --------- -----------
2           EMP1B     5           EMP2B     5
4           EMP1B     5           EMP2B     5
5           EMP1B     5           EMP2B     5

gbn · Answer 1 · 2011-06-14T19:35:06.600

Is it this simple?

SELECT
   vendorid,
   'EMP1A' AS employee, EMP1A AS [order1],
   'EMP2A' AS employee, EMP2A AS [order2]
FROM Mytable
UNION ALL
SELECT
   vendorid,
   'EMP1B', EMP1B,
   'EMP2B', EMP2B
FROM Mytable

Note: you have ambiguous column names which will break other things that use this data

For more rows, add UNION clauses

...
UNION ALL
SELECT
   vendorid,
   'EMP1C', EMP1C,
   'EMP2C', EMP2C
FROM Mytable
UNION ALL
SELECT
   vendorid,
   'EMP1D', EMP1D,
   'EMP2D', EMP2D
FROM Mytable
...

To filter further (need different column names)

SELECT ...
FROM
    (
    SELECT
       vendorid,
       'EMP1A' AS employee1, EMP1A AS [order1],
       'EMP2A' AS employee2, EMP2A AS [order2]
    FROM Mytable
    UNION ALL
    SELECT
       vendorid,
       'EMP1B', EMP1B,
       'EMP2B', EMP2B
    FROM Mytable
    UNION ALL
    SELECT
       vendorid,
       'EMP1C', EMP1C,
       'EMP2C', EMP2C
    FROM Mytable
    UNION ALL
    SELECT
       vendorid,
       'EMP1D', EMP1D,
       'EMP2D', EMP2D
    FROM Mytable
    ...
   ) foo
WHERE
   order1 = order2

thanks for the alternative. It helped me figured out what I was looking (see my self answer). I'll probably end up trying it to see if the perf is different . — Conrad Frix, Jun 14 '11 at 20:22

score 1 · Accepted Answer · answered Jun 14 '11 at 20:18

turns out I was working too hard

WITH piv1 
     AS (SELECT vendorid, 
                employee, 
                orders 
         FROM   @pvt p UNPIVOT (orders FOR employee IN (emp1a, emp1b, 
                                                        emp2a, emp2b) ) 
                AS unpvt) 
SELECT piv1.vendorid, 
       piv1.employee, 
       piv1.orders, 
       piv2.employee, 
       piv2.orders 
FROM   piv1 
       INNER JOIN piv1 piv2 
         ON piv1.vendorid = piv2.vendorid 
            AND piv1.employee <> piv2.employee 
            AND RIGHT(piv1.employee, 1) = RIGHT(piv2.employee, 1) 
WHERE  Substring(piv1.employee, 4, 1) <> '2' 
ORDER  BY piv1.vendorid, 
          piv1.employee

Now adding pairs just requires updating the UNPIVOT e.g.

@pvt p UNPIVOT (orders FOR employee IN (emp1a, emp1b,
                                        emp2a, emp2b,
                                        emp1c, emp2c,
                                        emp1d, emp2d) )

Better way to Partially UNPIVOT in Pairs in SQL

2 Answers2

Linked