How to use GROUP BY in a way that concatenates data in one column, but filters for specific data in another

I am running SQL Server 2014

I have a table that looks like this:

ID   | Name | AddressType | Address       | Features
 ========================================================
 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

I am trying to write a SQL Server query that looks for duplicate IDs and always returns the Line containing a "Work" Address, but I want it to concatenate the Features from both lines so that I get something like this:

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

The closest I've been able to figure out how to get looks something like this:

SELECT ID, Name, MAX(AddressType), MAX(Address), CONCAT(Features) FROM 
  (SELECT * FROM myTable ORDER BY ID, AddressType DESC)
GROUP BY ID, NAME

... but MAX(AddressType) and MAX(Address) are being pulled separately, so sometimes I get the Work Address and sometimes I get the Home Address. What I really need is the MAX(AddressType) and whatever Address is on the same line as that result.

The other thing I've tried is:

SELECT ID, Name, AddressType, Address, CONCAT(Features), COUNT(ID) OVER(PARTITION BY ID) AS IDcount
FROM myTable 
GROUP BY ID, NAME
WHERE AddressType = 'Work' OR IDcount = 1

... this always gives me all the right Addresses, but filters out the Features for the "Home" AddressType so that I can not Concatenate them in.

If you're using SQL Server 2017 or higher, you can use the STRING_AGG function to concatenate values within a group.

You can then use a CTE to fetch the Features list separately to simplify the query then join that back to your data to get the desired row plus the complete Features list for each ID.

Script:

;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

Output:

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

You can see a working example in this db<>fiddle.

The advantage of this method is that you can work with multiple address type values and order them as per your required logic by simply adding WHEN clauses to the CASE statement in the ROW_NUMBER() function in the inner SELECT statement.

UPDATE: For versions prior to SQL 2017, you can use a combination of STUFF and FOR XML to replicate the behaviour of STRING_AGG:

;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

See updated db<>fiddle with this solution. Functionally this behaves the same, though performance may be impacted.

StackExchange Database Administrators Q#299522, answer score: 7