Convert multiple rows in to multiple columns in SQL Server

user_1462821 0
Viewed 53

Dynamic SQL help:

Sample Data

dt_range type   amt     prct
2018     12     0.00    0.00
2018     19     3000    1.00
2019     15     107     0.5

desired output

dt_range    type12  type19  type15      type12_amt  type19_amt  type15_amt      type12_prct type19_prct     type15_prct
2018        12      19      null            0.00        3000        null            0.00        1.00        null
2019        null    null    15              null        null        107             null        null        0.5
pivot group-by tsql sql-server sql
1 Answers

If you are dealing with a predefined list of types, you can use conditional aggregation to pivot:

select
    dt_range,
    max(case when type = 12 then type end) type12,
    max(case when type = 19 then type end) type19,
    max(case when type = 15 then type end) type15,
    max(case when type = 12 then amt  end) type12_amt,
    max(case when type = 19 then amt  end) type19_amt,
    max(case when type = 15 then amt  end) type15_amt
    max(case when type = 12 then prct end) type12_prct,
    max(case when type = 19 then prct end) type19_prct,
    max(case when type = 15 then prct end) type15_prct
from mytable
group by dt_range
user_106767160
Related