SQL Help 'pivoting' a table in chunks

Question

My database table is such that each entry is listed in a new row for each chart. I want to 'pivot' and group by ID. Do I need to use a bunch of CASE WHEN statements or is this a succinct way of doing this?

Here's a simplified table:

Chart_ID	Rule	Action
A1	R1	Y
A1	R3	N
B4	R1	N
C1	R1	Y
C1	R2	N
C1	R3	Y

I'd like a table like this:

Chart_ID	R1	Action1	R2	Action2	R3	Action3
A1	R1	Y			R3	N
B4	R1	N
C1	R1	Y	R2	N	R3	Y

*"Do I need to use a bunch of CASE WHEN statements"* `CASE` **expressions**, but yes, you'll want to use use conditional aggregation, which uses `CASE` expressions inside an aggregate. What did you try? Why didn't it work? There are plenty of examples and questions on how to pivot data in SQL (Server). What about those didn't you understand? — Thom A, Jun 21 '21 at 13:14

score 0 · Accepted Answer · answered Jun 21 '21 at 15:27

You can use conditional aggregation:

select chart_id,
       max(case when rule = 'R1' then rule end) as r1,
       max(case when rule = 'R1' then action end) as action1,
       max(case when rule = 'R2' then rule end) as r2,
       max(case when rule = 'R2' then action end) as action2,
       max(case when rule = 'R3' then rule end) as r3,
       max(case when rule = 'R3' then action end) as action3,
       max(case when rule = 'R4' then rule end) as r4,
       max(case when rule = 'R4' then action end) as action4
from t
group by chart_id;

Note that the columns R1, R2, R3, and R4 are suspicious because they are redundant. As you have defined the data, action1 is for rule 'R1'.

SQL Help 'pivoting' a table in chunks

1 Answers1