String concatenation as an aggregate operator

It is sometimes useful to concatenate together a set of related string values; e.g. to concatenate into a comma-separated list:

• a list of column names for a single table;
• a list of tags for an article.

Intuitively, we would like to be able to do something like this:

SELECT 
  object_id
, COUNT(*) AS ColumnCount
, StrConcat(', ' + name) AS ColumnNames
FROM sys.columns
GROUP BY object_id

but have the problem that the StrConcat function doesn't exist!

A workaround is to use the FOR XML clause with PATH mode:

SELECT
  t.object_id
, COUNT(*) AS ColumnCount
, (
    SELECT 
      ', ' + name
    FROM sys.columns sub
    WHERE t.object_id = sub.object_id
    ORDER BY sub.column_id
    FOR XML PATH(''), TYPE
  ).value('.','VARCHAR(MAX)') AS ColumnList
FROM sys.columns t
GROUP BY t.object_id

You'll want to do something to remove the leading separator (in this case a comma) from the concatenated list – I haven't done so here to avoid muddying the core query structure. Wrapping it in STUFF(<select_query>, 1, 2, '') does the trick.

Generally, this query has the form:

SELECT
  t.GroupingField
, (
    SELECT 
      ', ' + FieldToConcatenate
    FROM TableName sub
    WHERE t.GroupingField = sub.GroupingField
    ORDER BY sub.FieldToOrderBy
    FOR XML PATH(''), TYPE
  ).value('.','VARCHAR(MAX)') AS AggregateValue
FROM TableName t
GROUP BY t.GroupingField

This got a whole lot easier!

SELECT
  t.object_id
, COUNT(*) AS ColumnCount
, STRING_AGG([name], ', ') AS ColumnList
FROM sys.columns t
GROUP BY t.object_id