add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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.