String Aggregate in SQL Server 2017

A common request when writing queries is to aggregate text values in a column into a single list of values with a separator, such as a comma. For example, a customer might have several purchases made by different salespeople, and we would like to generate a report that displays a list of all the salespeople associated with each customer. Something like this:

In previous versions of SQL Server, we could accomplish this with all kinds of methods. For example, we could use the PIVOT function or the FOR XML PATH clause, or we could implement a CLR aggregate function. All of these methods are quite complicated.

Finally, in SQL Server 2017, we have a new built-in T-SQL function - STRING_AGG. This function does all the hard work for us, and it's so much easier to use than the other methods mentioned above.

Here is how the query looks like:

SELECT
	CustomerId	= CustomerId ,
	SalesPeople	= STRING_AGG (SalesPerson , N',')
FROM
	Sales.Transactions
GROUP BY
	CustomerId
ORDER BY
	CustomerId ASC;

You can even add sorting to the text values within each group by using the WITHIN GROUP clause, like this:

SELECT
	CustomerId	= CustomerId ,
	SalesPeople	= STRING_AGG (SalesPerson , N',') WITHIN GROUP (ORDER BY SalesPerson ASC)
FROM
	Sales.Transactions
GROUP BY
	CustomerId
ORDER BY
	CustomerId ASC;

Here is a script you can download that creates the "Sales.Transactions" table, populates it with some data, and demonstrates the STRING_AGG function.

Enjoy!