STRING_AGG() – Concatenate values in Rows with Separator

SQL Server 2017 introduces a very helpful String function to concatenate values from rows as one value with separator and main advantage is it doesn’t add the separator in the end of the value

Syntax for the function is as follows

STRING_AGG ( expression, separator ) [
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] ) ]

Simple Example Usage – Building CSV from one field

SELECT String_Agg(strData, ‘,’) AS CSV FROM dbo.Test

Example Usage with ORDER BY option on different field

SELECT String_Agg(strData, ‘,’) WITHIN GROUP (ORDER BY intPK DESC) AS CSV FROM dbo.Test

Example Usage with GROUP BY and ORDER BY

SELECT
Category,
String_Agg(strData, ‘,’) WITHIN GROUP (ORDER BY intPK DESC) AS CSV1
FROM dbo.Test
GROUP BY Category

What is not supported ?

ORDER BY is supported only for one field or one set of fields and one direction only, , see below example, We cannot use ORDER BY intPK DESC for first STRING_AGG() and ORDER BY intPK ASC for next STRING_AGG

SELECT
Category,
String_Agg(strData, ‘,’) WITHIN GROUP (ORDER BY intPK DESC) AS CSV1,
String_Agg(strData, ‘,’) WITHIN GROUP (ORDER BY intPK ASC) AS CSV2
FROM dbo.Test
GROUP BY Category

 

The above query will return the below error message

Msg 8711, Level 16, State 1, Line 15
Multiple ordered aggregate functions in the same scope have mutually incompatible orderings.

Alternative way to get around this is use multiple queries and join them.

;WITH cteA
AS
(
SELECT
Category,
String_Agg(strData, ‘,’) WITHIN GROUP (ORDER BY intPK DESC) AS CSV1
FROM dbo.Test
GROUP BY Category
),
cteB
AS
(
SELECT
Category,
String_Agg(strData, ‘,’) WITHIN GROUP (ORDER BY intPK ASC) AS CSV2
FROM dbo.Test
GROUP BY Category
)
SELECT
A.Category, A.CSV1, B.CSV2
FROM cteA A
LEFT JOIN cteB B ON A.Category = B.Category

Hope you find this post helpful !!!