How to "unstack" data in SQL in Microsoft SQL Server-CodePudding

My table has this shape:

UTC_DT              ID  value
-----------------------------
2021-09-29 12:30:00 1   10
2021-09-29 12:30:00 2   20
2021-09-29 12:30:00 3   30
2021-09-29 12:45:00 1   11
2021-09-29 12:45:00 2   21
2021-09-29 12:45:00 3   31

I need this shape:

UTC_DT              1   2   3
------------------------------
2021-09-29 12:30:00 10  20  30
2021-09-29 12:45:00 11  21  31

I can't figure out how to do that. I thought maybe using PIVOT, but I can't figure out the correct syntax. Please help.

CodePudding user response：

You can do this with a simple conditional aggregation like so:

select utc_dt, 
    max(case id when 1 then value end) [1],
    max(case id when 2 then value end) [2],
    max(case id when 3 then value end) [3]
from t
group by utc_dt

CodePudding user response：

Since you asked for a pivot example, here you go:

WITH cte AS (
    SELECT * FROM (VALUES
        ('2021-09-29 12:30:00', 1, 10),
        ('2021-09-29 12:30:00', 2, 20),
        ('2021-09-29 12:30:00', 3, 30),
        ('2021-09-29 12:45:00', 1, 11),
        ('2021-09-29 12:45:00', 2, 21),
        ('2021-09-29 12:45:00', 3, 31)
    ) AS x(UTC_DT, ID, value)
)
SELECT pvt.*
FROM (
    SELECT *
    FROM cte
) AS src
PIVOT (
    MAX(value)
    FOR ID IN ([1], [2], [3])
) AS pvt;

It becomes rough when you don't know the set of (in this case) IDs that you want to pivot.