CODEWITHSUNDEEP
sqlt-sqltransformation
beta
beta

Reputation: 5676

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?

Upvotes: 1

Views: 165

Answers (3)

Sudesh Yadav
Sudesh Yadav

Reputation: 32

You can use unpivot.

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

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

Unpivot operator

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

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

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)

Upvotes: 4

Related Questions