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?
Advertisement
Answer
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 |
- Example db<>fiddle