Home > Back-end >  Convert rows to columns in SQL Server 2008
Convert rows to columns in SQL Server 2008

Time:02-26

We have one table called Licenses. This is what it looks like:

CustNum LicenseAddress License ExpiryDate
155 123 Y32CA 12/31/2018
155 998 Y32CB 12/31/2020
155 568 Y32CC 12/31/2022

Here is what I want it to look like:

LicAddr1 Lic1 ExpiryDate1 LicAddr2 Lic2 ExpiryDate2 LicAddr3 Lic3 ExpiryDate3
123 Y32CA 12/31/2018 998 Y32CB 12/31/2020 568 Y32CC 12/31/2022

Here is the query I have currently, however it’s only returning NULLs:

SELECT LicAddr1, 
       Lic1,
       ExpiryDate1,
       LicAddr2,
       Lic2,
       ExpiryDate2,
       LicAddr3,
       Lic3,
       ExpiryDate3
FROM (SELECT CustNum, LicenseAddress, License, ExpiryDate FROM Licenses) d
PIVOT (
       MAX(ExpiryDate) 
       FOR CustNum IN (LicAddr1, Lic1, ExpiryDate1, LicAddr2, Lic2, ExpiryDate2, LicAddr3, Lic3, ExpiryDate3) 
      ) piv

What am I doing wrong?

CodePudding user response:

PIVOT isn't really what you're after, especially since you're trying to get multiple values from each row (which doesn't work very well with the aggregation PIVOT tries to offer).

I'm assuming here you want the most recent three licenses, in which case we can apply a row number per CustNum, ordered by ExpiryDate (newest first), then flip them so they are left-to-right oldest-to-newest:

;WITH cte AS
(
  SELECT CustNum, LicenseAddress, License, ExpiryDate,
    rn = ROW_NUMBER() OVER (PARTITION BY CustNum ORDER BY ExpiryDate DESC)
  FROM dbo.Licenses
)
SELECT CustNum,
  LicAddr1    = MAX(CASE WHEN rn = 3 THEN LicenseAddress END),
  Lic1        = MAX(CASE WHEN rn = 3 THEN License        END),
  ExpiryDate1 = MAX(CASE WHEN rn = 3 THEN ExpiryDate     END),
  LicAddr2    = MAX(CASE WHEN rn = 2 THEN LicenseAddress END),
  Lic2        = MAX(CASE WHEN rn = 2 THEN License        END),
  ExpiryDate2 = MAX(CASE WHEN rn = 2 THEN ExpiryDate     END),
  LicAddr3    = MAX(CASE WHEN rn = 1 THEN LicenseAddress END),
  Lic3        = MAX(CASE WHEN rn = 1 THEN License        END),
  ExpiryDate3 = MAX(CASE WHEN rn = 1 THEN ExpiryDate     END)
FROM cte
GROUP BY CustNum;

Results:

CustNum LicAddr1 Lic1 ExpiryDate1 LicAddr2 Lic2 ExpiryDate2 LicAddr3 Lic3 ExpiryDate3
155 123 Y32CA 2018-12-31 998 Y32CB 2020-12-31 568 Y32CC 2022-12-31
  • Related