Oren Levy Oren Levy - 1 year ago 78
SQL Question

SQL convert rows to columns with static headers

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
1 AA
1 BB
1 CC
2 FF
2 LL
3 TT
3 YY
3 ZZ
3 XX
4 AA
4 BB
5 AA
6 WW
6 MM
6 PP

I need to display it like this.

I will never have more than 5 columns and would like to keep the column name static so I can join and refer to it easily.

ID SpCode1 |SpCode2 | SpCode3 | SpCode4 | SpCode5
1 AA |BB | CC
2 FF |LL
3 TT |YY |ZZ |XX
4 AA |BB
5 AA
6 WW |MM |PP

Much appreciated for any help.

PS: using SQL Server 2014



Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download