SQL Server STRING_AGG Function


By:

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]
simple str_agg example

STRING_AGG Exceeded Limit Error

The following will throw an error.

SELECT STRING_AGG(EmailAddress,',' ) as list
FROM [Person].[EmailAddress]
Error message: STRING_AGG aggregation result exceeded the limit of 8000 bytes. Use LOB types to avoid result truncation.

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]
str_example with convert function

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]
str_agg with within group clause

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
str_agg with joins group by

Related Articles


Last Update: 11/11/2021




Comments For This Article





download














get free sql tips
agree to terms