How to expand table based on column value SQL Server

Question

I have a table looks like this

+----+------+------+-------+
| ID | FY   | Code | Value |
+----+------+------+-------+
| 1  | 2021 | A    | 2     |
+----+------+------+-------+
| 1  | 2021 | B    | 5     |
+----+------+------+-------+
| 1  | 2021 | C    | 3     |
+----+------+------+-------+
| 2  | 2021 | A    | 4     |
+----+------+------+-------+
| 2  | 2021 | B    | 5     |
+----+------+------+-------+
| 2  | 2021 | C    | 6     |
+----+------+------+-------+

I want to expand the code column to the following format:

+----+------+---+---+---+
| ID | FY   | A | B | C |
+----+------+---+---+---+
| 1  | 2021 | 2 | 5 | 3 |
+----+------+---+---+---+
| 2  | 2021 | 4 | 5 | 6 |
+----+------+---+---+---+

I came up with an ugly way as to use multiple Where sub query and join them together, but there are a few values in 'Code' column which make things ugly.

Is there an elegant way of achieving this? (SQL Server)

Best,

The `Code` values are fixed or can change from period to period? (for instance, in `ID = 3 and FY=2021` you can have a `code = 'D'`) — Alrik, Aug 10 '20 at 09:26

score 2 · Accepted Answer · answered Aug 10 '20 at 09:20

2

Use conditional aggregation:

select
    id,
    fy,
    max(case when code = 'A' then value end) as A,
    max(case when code = 'B' then value end) as B,
    max(case when code = 'C' then value end) as C
from mytable
group by id, fy

answered Aug 10 '20 at 09:20

GMB

216,147
25
84
135

How to expand table based on column value SQL Server

1 Answers1