WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
<order_by_expression_list>
A list of non-constant expressions that can be used for sorting results. Only one order_by_expression is allowed per query. The default sort order is ascending.
Return Types
Return type depends on first argument (expression). If input argument is string type (NVARCHAR, VARCHAR), result type will be same as input type. The following table lists automatic conversions:
Input expression type
Result
int, bigint, smallint, tinyint, numeric, float, real, bit, decimal, smallmoney, money, datetime, datetime2,
NVARCHAR(4000)
STRING_AGG is an aggregate function that takes all expressions from rows and concatenates them into a single string. Expression values are implicitly converted to string types and then concatenated. The implicit conversion to strings follows the existing rules for data type conversions. For more information about data type conversions, see CAST and CONVERT (Transact-SQL).
If the input expression is type VARCHAR, the separator cannot be type NVARCHAR.
Null values are ignored and the corresponding separator is not added. To return a place holder for null values, use the ISNULL function as demonstrated in example B.
STRING_AGG is available in any compatibility level.
<order_clause> is available with database compatibility level 110 and above.
Examples
Most of the examples in this article reference the AdventureWorks sample databases.
A. Generate list of names separated in new lines
The following example produces a list of names in a single result cell, separated with carriage returns.
USE AdventureWorks2022;
SELECT STRING_AGG (CONVERT(NVARCHAR(max),FirstName), CHAR(13)) AS csv
FROM Person.Person;
Here's the result set.
If using the SQL Server Management Studio Query Editor, the Results to Grid option cannot implement the carriage return. Switch to Results to Text to see the result set properly.
Results to Text are truncated to 256 characters by default. To increase this limit, change the Maximum number of characters displayed in each column option.
B. Generate list of names separated with comma without NULL values
The following example replaces null values with 'N/A' and returns the names separated by commas in a single result cell.
USE AdventureWorks2022;
SELECT STRING_AGG(CONVERT(NVARCHAR(max), ISNULL(FirstName,'N/A')), ',') AS csv
FROM Person.Person;
Here's the result set.
Results are shown trimmed.
USE AdventureWorks2022;
SELECT STRING_AGG(CONVERT(NVARCHAR(max), CONCAT(FirstName, ' ', LastName, '(', ModifiedDate, ')')), CHAR(13)) AS names
FROM Person.Person;
Here's the result set.
Results are shown trimmed.
If using the Management Studio Query Editor, the Results to Grid option cannot implement the carriage return. Switch to Results to Text to see the result set properly.
D. Return news articles with related tags
Imagine a database where articles and their tags are separated into different tables. A developer wants to return one row per each article with all associated tags. The following query achieves this result:
SELECT a.articleId, title, STRING_AGG (tag, ',') as tags
FROM dbo.Article AS a
LEFT JOIN dbo.ArticleTag AS t
ON a.ArticleId = t.ArticleId
GROUP BY a.articleId, title;
Here's the result set.
articleId
title
The GROUP BY clause is required if the STRING_AGG function isn't the only item in the SELECT list.
E. Generate list of emails per towns
The following query finds the email addresses of employees and groups them by city:
USE AdventureWorks2022;
SELECT TOP 10 City, STRING_AGG(CONVERT(NVARCHAR(max), EmailAddress), ';') AS emails
FROM Person.BusinessEntityAddress AS BEA
INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID
INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID
GROUP BY City;
Here's the result set.
Results are shown trimmed.
Ballard
paige28@adventure-works.com;joshua24@adventure-works.com;javier12@adventure-works.com;...
Baltimore
gilbert9@adventure-works.com
Barstow
kristen4@adventure-works.com
Basingstoke Hants
dale10@adventure-works.com;heidi9@adventure-works.com
Baytown
kelvin15@adventure-works.com
Beaverton
billy6@adventure-works.com;dalton35@adventure-works.com;lawrence1@adventure-works.com;...
Bell Gardens
christy8@adventure-works.com
Bellevue
min0@adventure-works.com;gigi0@adventure-works.com;terry18@adventure-works.com;...
Bellflower
philip0@adventure-works.com;emma34@adventure-works.com;jorge8@adventure-works.com;...
Bellingham
christopher23@adventure-works.com;frederick7@adventure-works.com;omar0@adventure-works.com;...
Emails returned in the emails column can be directly used to send emails to group of people working in some particular cities.
F. Generate a sorted list of emails per towns
Similar to the previous example, the following query finds the email addresses of employees, groups them by city, and sorts the emails alphabetically:
USE AdventureWorks2022;
SELECT TOP 10 City, STRING_AGG(CONVERT(NVARCHAR(max), EmailAddress), ';') WITHIN GROUP (ORDER BY EmailAddress ASC) AS Emails
FROM Person.BusinessEntityAddress AS BEA
INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID
INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID
GROUP BY City;
Here's the result set.
Results are shown trimmed.
Carnation
don0@adventure-works.com;douglas0@adventure-works.com;george0@adventure-works.com;...
Boulogne-Billancourt
allen12@adventure-works.com;bethany15@adventure-works.com;carl5@adventure-works.com;...
Berkshire
barbara41@adventure-works.com;brenda4@adventure-works.com;carrie14@adventure-works.com;...
Berks
adriana6@adventure-works.com;alisha13@adventure-works.com;arthur19@adventure-works.com;...
Next steps
Learn more about Transact-SQL functions in the following articles:
STRING_ESCAPE (Transact-SQL)
STUFF (Transact-SQL)
CONCAT (Transact-SQL)
CONCAT_WS (Transact-SQL)
Aggregate Functions (Transact-SQL)
String Functions (Transact-SQL)
