sql server - SQL Unpivot multiple columns Data -

i using sql server 2008 , trying unpivot data , here sql code using,

create table #pvt1 (vendorid int, sa int, emp1 int,sa1 int,emp2 int) go insert #pvt1  values (1,2,4,3,9);  go  --unpivot table. select distinct vendorid,orders,orders1     (select vendorid, emp1, sa,emp2,sa1    #pvt1 ) p unpivot    (orders emp in        (emp1,emp2) )as unpvt unpivot    (orders1 emp1 in        (sa,sa1) )as unpvt1; go 

and here result of above code.

vendorid    orders  orders1  1   4   2  1   4   3  1   9   2  1   9   3 

but want output way indicated below

vendorid    orders  orders1  1   4   2  1   9   3 

the relationship above code 2 related 4 , 3 related 9 .

how can acheieve ?

an easier way unpivot data use cross apply unpivot columns in pairs:

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

see sql fiddle demo. or can use cross apply values clause if don't want use union all:

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

see sql fiddle demo