Concatenating a list of strings in T-SQL

Remember how clumsy it was to concatenate string identifiers of child records within a sub-SELECT and adding this artificial FOR XML PATH('')? (if you don’t remember)

Writing plain SQL again after a long time, I found the “new” function STRING_AGG (introduced in SQL Server 2017) to be a great simplification. I guess mainly because the sub-select can be inlined and the whole SELECT can be GROUP BY’d.

Case in point, I needed to find out which database tables have unique indexes defined, and which columns they indexed.

Here’s the magic:

select o.name, i.name,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.index_column_id)
from sys.index_columns ic
inner join sys.indexes i 
    on ic.object_id = i.object_id and ic.index_id = i.index_id
inner join sys.objects o on i.object_id = o.object_id
inner join sys.columns c 
    on ic.object_id = c.object_id and ic.column_id = c.column_id
where o.is_ms_shipped = 0 
and o.name not like '%_history'
and i.is_primary_key = 0
and i.is_unique = 1
group by o.name, i.name
order by 1, 2