I have a data set that I need to transform from rows to columns.
I looked at pivot and does not really answer the problem since I need specific static column headers (not matching the data names).
Here is how the current data is stored:
Id | SpCode
ID SpCode1 |SpCode2 | SpCode3 | SpCode4 | SpCode5
1 AA |BB | CC
2 FF |LL
3 TT |YY |ZZ |XX
4 AA |BB
6 WW |MM |PP
One method is
pivot. Another is conditional aggregation. I find the latter easier to use:
select id, max(case when seqnum = 1 then spcode end) as spcode_1, max(case when seqnum = 2 then spcode end) as spcode_2, max(case when seqnum = 3 then spcode end) as spcode_3, max(case when seqnum = 4 then spcode end) as spcode_4, max(case when seqnum = 5 then spcode end) as spcode_5 from (select t.*, row_number() over (partition by id order by spcode) as seqnum from t ) t group by id;