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
