By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.Licenses(CustNum int, LicenseAddress int,
License varchar(10), ExpiryDate date);
INSERT dbo.Licenses VALUES
(155, 123, 'Y32CA', '20181231'),
(155, 998, 'Y32CB', '20201231'),
(155, 568, 'Y32CC', '20221231');
3 rows affected
;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;
| 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 |
Warning: Null value is eliminated by an aggregate or other SET operation.