Home > SQL Server, SQL Server 2017 > STRING_AGG() – Concatenate values in Rows with Separator

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

capture20171005142123779

Example Usage with ORDER BY option on different field

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

capture20171005142257083

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

capture20171005142405536

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

capture20171005143658060

Hope you find this post helpful !!!

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: