clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2249115 fiddles created (32115 in the last week).

CREATE TABLE Addresses ( ID INT, Name VARCHAR(100), AddressType VARCHAR(100), Address VARCHAR(100), Features VARCHAR(100) )
 hidden batch(es)


INSERT INTO Addresses (ID, Name, AddressType, Address, Features) VALUES (1, 'Bob', 'Home', '123 Nope St', 'JP') ,(2, 'John', 'Work', '555 Fake St', 'MNGF') ,(2, 'John', 'Home', '654 Madeup Ln', 'IMP JP') ,(3, 'Kim', 'Work', '92 Nadda Blvd', 'MP')
4 rows affected
 hidden batch(es)


;WITH AddressFeatures AS ( SELECT ID, STRING_AGG(Features, ' ') AS Features FROM Addresses GROUP BY ID ) SELECT s1.ID, s1.Name, s1.AddressType, s1.Address, af.Features FROM ( SELECT ID, Name, AddressType, Address , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY CASE WHEN AddressType = 'Work' THEN 1 ELSE 2 END) AS AddressTypeOrder FROM Addresses ) s1 INNER JOIN AddressFeatures af ON af.ID = s1.ID WHERE AddressTypeOrder = 1
ID Name AddressType Address Features
1 Bob Home 123 Nope St JP
2 John Work 555 Fake St MNGF IMP JP
3 Kim Work 92 Nadda Blvd MP
 hidden batch(es)