Convert columns into rows for each ID

问题:

I have a table that looks as follows

ID  | value1 | value2 | value3
1   | 5      | 6      | 7
2   | 10     | 11     | 12
3   | 20     | 21     | 22

I need to create one row for each of the 3 value columns per ID. The final table should look like this (the column headers dont make sense anymore, but you can ignore this):

ID  | value1 | value2
1   | value1 | 5     
1   | value2 | 6     
1   | value3 | 7     
2   | value1 | 10     
2   | value2 | 11    
2   | value3 | 12    
3   | value1 | 20     
3   | value2 | 21    
3   | value3 | 22    

What would be the best way to approach this in TSQL?

回答1:

You need to unpivot the data you can use CROSS APPLY for that

Select Id,cs.name,cs.values_list from yourtable 
cross apply (values ('value1',value1),
                    ('value2',value2),
                    ('value3',value3)) Cs (name,values_list)

回答2:

Unpivot operator

select  id,value1,value2
from    t unpivot (value2 for value1 in (value1,value2,value3)) u

回答3:

You can use unpivot.

Select ID,vals,val from (Select * from stack) stc UNPIVOT (val for vals in (value1,value2,value3) ) as vals1