sqlsql-serverpivot-tablesql-server-2019

Pivot multiple columns


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;

Solution

  • 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;
    

    SQL Fiddle