====== 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.

===== SQL Server 2016 and earlier =====

Intuitively, we would like to be able to do something like this:
<code tsql>
SELECT 
  object_id
, COUNT(*) AS ColumnCount
, StrConcat(', ' + name) AS ColumnNames
FROM sys.columns
GROUP BY object_id
</code>
but have the problem that the ''StrConcat'' function doesn't exist!

==== Workaround ====
A workaround is to use the ''[[http://technet.microsoft.com/en-us/library/ms178107(v=sql.110).aspx|FOR XML]]'' clause with ''[[http://technet.microsoft.com/en-us/library/ms189885.aspx|PATH]]'' mode:

<code tsql>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
</code>
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, <nowiki>''</nowiki>'') does the trick.

==== Boilerplate ====
Generally, this query has the form:
<code tsql>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
</code>

===== SQL Server 2017 and beyond =====

This got a whole lot easier!

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



{{tag> tsql aggregate}}