I have a vertical table that end users want converted to a horizontal table with some groupings. I'm writing this in SQL Server 2019.
My table looks like this:
| PKID | cID | vID | vDate |
|---|---|---|---|
| 1 | 2 | 81 | 1996-04-04 |
| 2 | 2 | 82 | 1999-05-20 |
| 3 | 3 | 81 | 2000-01-01 |
| 4 | 3 | 82 | 2005-03-17 |
| 5 | 3 | 18 | 2010-08-30 |
| 6 | 3 | 19 | 2015-02-01 |
The desired output would be:
| cID | vID_1 | vDate_1 | vID_2 | v_Date_2 | vID_3 | vDate_3 | vID_4 | vDate_4 |
|---|---|---|---|---|---|---|---|---|
| 2 | 81 | 1996-04-04 | 82 | 1999-05-20 | NULL | NULL | NULL | |
| 3 | 81 | 2000-01-001 | 82 | 2005-03-17 | 18 | 2010-08-30 | 19 | 2015-02-01 |
I have code to get me part of the way there but I can't figure out how to get all of it. The below code will produce this table. The dates in the second row should also be sorted ascending. But the bigger issue is that when I try to add vID_1, vID_2, etc. it adds rows for each record. I know I'm missing something simple but cannot figure it out.
| cID | vDate_1 | vDate_2 | vDate_3 | vDate_4 |
|---|---|---|---|---|
| 2 | 1996-04-04 | 1999-05-20 | NULL | NULL |
| 3 | 2010-08-30 | 2015-02-01 | 2000-01-01 | 2005-03-17 |
CREATE TABLE #tmp (PKID int, cID int, vID int, vDate date)
INSERT INTO #tmp (PKID, cID, vID, vDate)
VALUES (1,2,81,'1996-04-04'),
(2,2,81,'1999-05-20'),
(3,3,81,'2000-01-01'),
(4,3,82,'2005-03-17'),
(5,3,18,'2010-08-30'),
(6,3,19,'2015-02-01');
SELECT cID
,[1] AS vDate_1
,[2] AS vDate_2
,[3] AS vDate_3
,[4] AS vDate_4
FROM (
SELECT cID
,vDate
,ROW_NUMBER() OVER (PARTITION BY cID ORDER BY vID) AS RN
FROM #tmp
GROUP BY cID,vID, vDate
) vax
PIVOT
(
MAX(vDate)
FOR RN IN ([1],[2],[3],[4])
) as pvt
DROP TABLE #tmp;
It's generally much easier to do pivots manually using MAX(CASE. You can just specify which columns you want to aggregate, and therefore you can pivot multiple columns at once. No joins are needed.
Also your ordering in the row-number is wrong.
SELECT
cID,
MAX(CASE WHEN RN = 1 THEN vID END) AS vID_1,
MAX(CASE WHEN RN = 1 THEN vDate END) AS vDate_1,
MAX(CASE WHEN RN = 2 THEN vID END) AS vID_2,
MAX(CASE WHEN RN = 2 THEN vDate END) AS vDate_2,
MAX(CASE WHEN RN = 3 THEN vID END) AS vID_3,
MAX(CASE WHEN RN = 3 THEN vDate END) AS vDate_3,
MAX(CASE WHEN RN = 4 THEN vID END) AS vID_4,
MAX(CASE WHEN RN = 4 THEN vDate END) AS vDate_4
FROM (
SELECT
cID,
vID,
vDate,
ROW_NUMBER() OVER (PARTITION BY cID ORDER BY vDate) AS RN
FROM #tmp
) vax
GROUP BY
cID;