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