SQL Unpivot multiple columns Data

Question

I am using SQL server 2008 and I am trying to unpivot the data. Here is the SQL code that I am using,

CREATE TABLE #pvt1 (VendorID int, Sa int, Emp1 int,Sa1 int,Emp2 int) GO INSERT INTO #pvt1  VALUES (1,2,4,3,9);  GO  --Unpivot the table. SELECT distinct VendorID,Orders,Orders1 FROM     (SELECT VendorID, Emp1, Sa,Emp2,Sa1    FROM #pvt1 ) p UNPIVOT    (Orders FOR Emp IN        (Emp1,Emp2) )AS unpvt UNPIVOT    (Orders1 FOR Emp1 IN        (Sa,Sa1) )AS unpvt1; GO 

And Here is the result of the above code.

VendorID    Orders  Orders1 1            4      2 1            4      3 1            9      2 1            9      3 

But I want my Output to be the way indicated below

VendorID    Orders  Orders1 1           4       2 1           9       3 

The relationship from the above code is 2 is related to 4, and 3 is related to 9.

How can I achieve this?

Answer 1

An easier way to unpivot the data would be to use a CROSS APPLY to unpivot the columns in pairs:

select vendorid, orders, orders1 from pvt1 cross apply (   select emp1, sa union all   select emp2, sa1 ) c (orders, orders1); 

See SQL Fiddle with Demo. Or you can use CROSS APPLY with the VALUES clause if you don't want to use the UNION ALL:

select vendorid, orders, orders1 from pvt1 cross apply (   values      (emp1, sa),     (emp2, sa1) ) c (orders, orders1); 

See SQL Fiddle with Demo

Answer 2

The answer by Taryn is indeed super useful, and I'd like to expand one aspect of it.

If you have a very un-normalized table like this, with multiple sets of columns for e.g. 4 quarters or 12 months:

+-------+------+------+------+------+------+------+-------+------+ | cYear | foo1 | foo2 | foo3 | foo4 | bar1 | bar2 | bar3  | bar4 | +-------+------+------+------+------+------+------+-------+------+ |  2020 |   42 |  888 |    0 |   33 | one  | two  | three | four | +-------+------+------+------+------+------+------+-------+------+ 

Then the CROSS APPLY method is easy to write and understand, when you got the hang of it. For the numbered column, use constant values.

SELECT      cYear,     cQuarter,     foo,     bar FROM temp  CROSS APPLY (   VALUES     (1, foo1, bar1),     (2, foo2, bar2),     (3, foo3, bar3),     (4, foo4, bar4)  ) c (cQuarter, foo, bar) 

Result:

+-------+----------+-----+-------+ | cYear | cQuarter | foo |  bar  | +-------+----------+-----+-------+ |  2020 |        1 |  42 | one   | |  2020 |        2 | 888 | two   | |  2020 |        3 |   0 | three | |  2020 |        4 |  33 | four  | +-------+----------+-----+-------+ 

SQL Fiddle