'TSQL - list multiple records across multiple columns
is there a way to list multiple records across multiple columns eg. 2 columns? There must be no link between the records that are present in the same row.
current:
| id | date | hours |
|---|---|---|
| 1 | 01/01/2022 | M |
| 2 | 02/01/2022 | P |
| 3 | 07/01/2022 | P |
| 7 | 01/01/2022 | N |
result:
| id | date | hours | id | date | hours |
|---|---|---|---|---|---|
| 1 | 01/01/2022 | M | 2 | 02/01/2022 | P |
| 3 | 07/01/2022 | P | 7 | 01/01/2022 | N |
Thanks for your help
i tried with 3 columns:
WITH cte AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY id) rn
FROM yourTable
)
SELECT MAX(CASE WHEN rn % 3 = 2 THEN id END) AS id1,
MAX(CASE WHEN rn % 3 = 2 THEN date END) AS date1,
MAX(CASE WHEN rn % 3 = 2 THEN hours END) AS hours1,
MAX(CASE WHEN rn % 3 = 1 THEN id END) AS id2,
MAX(CASE WHEN rn % 3 = 1 THEN date END) AS date2,
MAX(CASE WHEN rn % 3 = 1 THEN hours END) AS hours2
MAX(CASE WHEN rn % 3 = 0 THEN id END) AS id3,
MAX(CASE WHEN rn % 3 = 0 THEN date END) AS date3,
MAX(CASE WHEN rn % 3 = 0 THEN hours END) AS hours3
FROM cte
GROUP BY (rn - 1) / 3;
but the result is not sorted by id. The result is not the following:
| id1 | date1 | hours1 | id2 | date2 | hours2 | id3 | date3 | hours3 |
|---|---|---|---|---|---|---|---|---|
| 1 | 01/01/2022 | M | 2 | 02/01/2022 | P | 3 | 07/01/2022 | P |
| 7 | 01/01/2022 | N |
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
