Im trying to turn a single column of data,
| id |
|---|
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
into:
| col1 | col2 | col3 |
|---|---|---|
| 1 | 2 | 3 |
| 4 | 5 | 6 |
I figured it would be something to do with pivots but I’m still unsure how to accomplish this.
>Solution :
We can try using a pivot query with the help of ROW_NUMBER:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn
FROM yourTable
)
SELECT
MAX(CASE WHEN rn % 3 = 1 THEN id END) AS col1,
MAX(CASE WHEN rn % 3 = 2 THEN id END) AS col2,
MAX(CASE WHEN rn % 3 = 0 THEN id END) AS col3
FROM cte
GROUP BY (rn - 1) / 3;