Convert multiple rows in to multiple columns in SQL Server

Question

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

GMB · Accepted Answer · 2020-03-31 18:17:14Z

3

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

answered Mar 31, 2020 at 18:17

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert multiple rows in to multiple columns in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related