SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE Addresses (
  ID INT, 
  Name VARCHAR(100), 
  AddressType VARCHAR(100), 
  Address VARCHAR(100), 
  Features VARCHAR(100)
)
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

;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

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