Concatenating SQL row values into a summary field

Question

I have customers that can belong to many groups at the same time. I need to create super-groups out of the distinct groups, in alphabetical order.

Cust_ID|Group
1      |Group 4
1      |Group 6
1      |Group 6
1      |Group 6
1      |Group 5
2      |Group 3
2      |Group 1
2      |Group 3

Desired outcome is

Cust_ID|Supergroup
1      |Group 4, Group 5, Group 6
2      |Group 1, Group 3

What I have tried

1. STRING_AGG STRING_AGG - not available in my SQL server version (I'm using 2016)

2. FOR XML PATH It's a bit hacky, but I've managed to get concatenation of all records in one field.

   Select SUBSTRING( 
   ( 
        SELECT top 15 PolicyOwner1_CISKey, ',' + clientGroup AS 'data()'
            FROM IND_IN_SCOPE FOR XML PATH('') ), 2 , 9999) As Groups
   

3. PIVOT From my reading, it only seems to handle numerics, it's strings I'm needing to be aggregated.

Answer 1

You can try to use right FOR XML PATH and DISTINCT subquery.

SELECT 
  [Cust_ID],
  STUFF((
    SELECT DISTINCT ', ' + [Group] 
    FROM IND_IN_SCOPE
    WHERE (Cust_ID = t1.Cust_ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS Supergroup
FROM IND_IN_SCOPE t1
GROUP BY Cust_ID

sqlfiddle