Overview
The STRING_AGG function concatenates strings separated by a specified separator. This was introduced with SQL Server 2017.
Explanation
Syntax
STRING_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]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]
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
Additional Information
