I'm trying to pivot a table from
ID | DATE | DATA |
---|---|---|
12345 | 2022-05-01 | "Stringvalue.. rank:1" |
12345 | 2022-05-01 | "Stringvalue.. rank:2" |
67890 | 2022-05-01 | "Stringvalue.. rank:2" |
67890 | 2022-05-01 | "Stringvalue.. rank:2" |
~
ID | DATE | DATA |
---|---|---|
12345 | 2022-05-01 | "Stringvalue.. rank:23" |
12345 | 2022-05-01 | "Stringvalue.. rank:24" |
67890 | 2022-05-01 | "Stringvalue.. rank:23" |
67890 | 2022-05-01 | "Stringvalue.. rank:24" |
to
ID | DATE | rank 1 | rank 2 | ... | rank 24 |
---|---|---|---|---|---|
12345 | 2022-05-01 | "Stringvalue.. rank:1" | "Stringvalue.. rank:2" | "Stringvalue.. rank:23" | |
67890 | 2022-05-01 | "Stringvalue.. rank:1" | "Stringvalue.. rank:2" | "Stringvalue.. rank:24" |
referring to this: Dynamic Pivot Needed with Row_Number(), I tried implementing this by
SELECT DISTINCT ID, Date,
(select ct.Data from cte ct where ct.ID= cte.ID and ct.Date = cte.Date and ct.rank =1) AS Ghour_1,
(select ct.Data from cte ct where ct.ID = cte.ID and ct.Date= cte.Date and ct.rank =2) AS Ghour_2,
.
.
.
ct.Date = cte.Date and ct.rank =23) AS Ghour_23,
(select ct.Data from cte ct where ct.ID = cte.ID and ct.Date = cte.Date and ct.rank =24) AS Ghour_24,
from cte
but it is throwing resources exceeded error, is this because I have too many subquery select statements? Could anyone please suggest how to optimise this query?
Use below as a starting point
if applied to sample data in your question - output is
Above is easy to transform to dynamic pivot - see multiple examples here on SO - in particular at least few of my answers dedicated to this