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

Question

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.

Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Thom A, Oct 08 '21 at 13:59
I think so, I just don't understand whats happening. I've got some time later this week to work on this again. Thank you for taking the time to edit my question and reply! — XiB, Oct 12 '21 at 14:11

score 2 · Answer 1 · answered Oct 08 '21 at 14:36

2

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

answered Oct 08 '21 at 14:36

Stu

30,392
6
14
33

score 1 · Answer 2 · answered Oct 08 '21 at 20:17

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.

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

2 Answers2