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. 2249313 fiddles created (32225 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, (SELECT STUFF(( SELECT ' ' + a2.[Features] FROM Addresses a2 WHERE a1.ID = a2.ID FOR XML PATH ('')), 1, 1, '')) AS Features FROM Addresses a1 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)