Concatenate Multiple Rows Using STRING_AGG

STRING_AGG is a new built-in string function introduced in SQL Server 2017. This function can be used to concatenate multiple rows of data into single string. Prior to SQL Server 2017, we used to concatenate rows using different workarounds like using FOR XML PATH, COALESCE, etc… Now from SQL Server 2017 onward, we have a straightforward method in the form of STRING_AGG. In this article I will explain with examples to concatenate multiple rows of data using STRING_AGG and the various options in it. The syntax for using this function is:

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

Using STRING_AGG

The simplest way to use STRING_AGG is to provide the column name for the expression argument and a separator. The the expression argument will take any data type and it is converted to VARCHAR or NVARCHAR while concatenating, but the separator argument has to be a VARCHAR or NVARCHAR. Here is an example:

To start with, lets create a table with tow columns. In this example, I’ve named one column as City and another column as CustomerName.

/* Lets create a table */
Create Table CustomerNames (
	City Varchar(50),
	CustomerName Varchar(50)
)
GO

/* and insert data in it */
INSERT INTO CustomerNames
	SELECT 'Los Angeles', 'Russell' UNION ALL
	SELECT 'Long Beach', 'Tiffany' UNION ALL
	SELECT 'Fremont', 'Wayne' UNION ALL
	SELECT 'Los Angeles', 'Eugene' UNION ALL
	SELECT 'Fremont', 'Theresa' UNION ALL
	SELECT 'Long Beach', 'Willie' UNION ALL
	SELECT 'Los Angeles', 'Hannah' UNION ALL
	SELECT 'Fremont', 'Sean' UNION ALL
	SELECT 'Long Beach', 'Terry' UNION ALL
	SELECT 'Long Beach', 'Jeremy' UNION ALL
	SELECT 'Los Angeles', 'Megan' UNION ALL
	SELECT 'Los Angeles', 'Lauren' 
GO

/* Verify the data */
Select * from CustomerNames
GO

/* Result */
City          CustomerName
--------------------------
Los Angeles   Russell
Long Beach    Tiffany
Fremont       Wayne
Los Angeles   Eugene
Fremont       Theresa
Long Beach    Willie
Los Angeles   Hannah
Fremont       Sean
Long Beach    Terry
Long Beach    Jeremy
Los Angeles   Megan
Los Angeles   Lauren

Now, let’s concatenate all the rows of the column CustomerName using STRING_AGG function providing the column name and comma separator as inputs. This is the simplest way of using STRING_AGG function.

Select 
	STRING_AGG(CustomerName, ',') As AllCustomers 
	From CustomerNames
GO

/* Result */
AllCustomers
------------------------------------------------------------------------
Russell,Tiffany,Wayne,Eugene,Theresa,Willie,Hannah,Sean,Terry,Jeremy,Megan,Lauren

(1 row affected)

Using The Order Clause WITHIN GROUP

If you notice the above result, all the customer names are concatenated in the order as they are stored in the table. To concatenate and sort them in ascending or descending order, you can use the WITHIN GROUP clause. In this clause you can specify ORDER BY the column within brackets. By default, the sorting is done in ascending order. You can explicitly specify ascending or descending order (ASC | DESC).

/* To order the names in ascending order*/
Select 
	STRING_AGG(CustomerName, ',') WITHIN GROUP (ORDER BY CustomerName) As AllCustomers 
	From CustomerNames
GO

/* Result */
AllCustomers
------------------------------------------------------------------------
Eugene,Hannah,Jeremy,Lauren,Megan,Russell,Sean,Terry,Theresa,Tiffany,Wayne,Willie

(1 row affected)

Descending Order

To concatenate the customer names in descending order specify DESC.

/* To order the names in descending order*/
Select 
	STRING_AGG(CustomerName, ',') WITHIN GROUP (ORDER BY CustomerName DESC) As AllCustomers 
	From CustomerNames
GO

/* Result */
AllCustomers
------------------------------------------------------------------------
Willie,Wayne,Tiffany,Theresa,Terry,Sean,Russell,Megan,Lauren,Jeremy,Hannah,Eugene

(1 row affected)

Concatenate Based on Groups

In all the above examples the rows of CustomerName column is concatenated without any grouping. If you want to group customer names based on their cities, then you can use the regular GROUP BY clause in the select query. Similarly you can use the WHERE conditions to filter out some rows. Here is an example for grouping the names based on cities and concatenate them:

/* Grouping */
SELECT 
	City,
	STRING_AGG(CustomerName, ',') 
		WITHIN GROUP (ORDER BY CustomerName DESC) As AllCustomers 
	FROM CustomerNames
	GROUP BY City
GO

/* Result */
City	        AllCustomers
-----------------------------------------------------------------------
Fremont	        Wayne,Theresa,Sean
Long Beach	Willie,Tiffany,Terry,Jeremy
Los Angeles	Russell,Megan,Lauren,Hannah,Eugene

Related

• Read article about generating row number without using order by clause.

Reference

• About STRING_AGG at Microsoft Docs Website.