SQL Server STRING_AGG Function

By: Daniel Calbimonte

The STRING_AGG function concatenates strings separated by a specified separator. This was introduced with SQL Server 2017.

Syntax

STR_AGG(expression, separatorString) [WITHIN GROUP ( ORDER BY order_ expression ASC | DESC]

Parameters

• expression - this is the string that we want to concatenate with a separator.
• separatorString – a character string used to separate strings.
• orderExpression – a list of expressions used to sort the output.

Simple STRING_AGG Example

The following example will rollup all currency names and separate each using a forward slash.

SELECT STRING_AGG(name,'/') as output
FROM [Sales].[Currency]

STRING_AGG Exceeded Limit Error

The following will throw an error.

SELECT STRING_AGG(EmailAddress,',' ) as list
FROM [Person].[EmailAddress]

By default, the STRING_AGG is a varchar with a limit of 8000 characters. If the values concatenated exceed 8000 characters, you will need to CONVERT to nvarchar(max) or varchar(max) as follows.

SELECT STRING_AGG(CONVERT(NVARCHAR(max),EmailAddress),',' ) as list
FROM [Person].[EmailAddress]

STRING_AGG Not a Recognized Function Error

STRING_AGG was implemented in SQL Server 2017. If your SQL Server is a lower version, you would need to upgrade to SQL Server 2017 or later.

The following link may help you find your current version: How to tell what SQL Server versions you are running.

Using STRING_AGG with WITHIN GROUP Example

The following example shows how to display the list of email addresses from the EmailAddress table separated by commas and order in descending order using the WITHIN GROUP clause.

SELECT STRING_AGG(CONVERT(NVARCHAR(max),EmailAddress),',' ) WITHIN GROUP (ORDER BY EmailAddress desc) as list
FROM [Person].[EmailAddress]

GROUP BY with the STRING_AGG Function

The following example shows the product names and all the sales order numbers separated by commas for the corresponding product. The example joins the Product table and SalesOrderDetail tables.

SELECT p.[Name], STRING_AGG(CONVERT(NVARCHAR(max),[SalesOrderID]),',' ) as salesorders
FROM [Production].[Product] p
JOIN [Sales].[SalesOrderDetail] s ON p.ProductID=s.ProductID
GROUP BY P.Name

Related Articles

• Solve old problems with SQL Server’s new STRING_AGG and STRING_SPLIT functions
• Learn how to convert data with SQL CAST and SQL CONVERT