Concatenates the values of string expressions and places separator values between them. The separator is not added at the end of string.
Transact-SQL Syntax Conventions
STRING_AGG ( expression, separator ) [ <order_clause> ]
<order_clause> ::=
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
expression
Is an expression of any type. Expressions are converted to NVARCHAR
or VARCHAR
types during concatenation. Non-string types are converted to NVARCHAR
type.
separator
Is an expression of NVARCHAR
or VARCHAR
type that is used as separator for concatenated strings. It can be literal or variable.
Optionally specify order of concatenated results using WITHIN GROUP
clause:
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
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 type is 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 |
---|---|
NVARCHAR(MAX) | NVARCHAR(MAX) |
VARCHAR(MAX) | VARCHAR(MAX) |
NVARCHAR(1…4000) | NVARCHAR(4000) |
VARCHAR(1…8000) | VARCHAR(8000) |
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.
The following example produces a list of names in a single result cell, separated with carriage returns.
Here is the result set.
csv |
---|
Syed Catherine Kim Kim Kim Hazem … |
NULL
values found in name
cells are not returned in result.
> [!NOTE]
> 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.
The following example replaces null values with ‘N/A’ and returns the names separated by commas in a single result cell.
Here is the result set.
Csv |
---|
John,N/A,Mike,Peter,N/A,N/A,Alice,Bob |
SELECT
STRING_AGG(CONCAT(FirstName, ' ', LastName, ' (', ModifiedDate, ')'), CHAR(13))
AS names
FROM Person.Person;
Here is the result set.
names |
---|
Ken Sánchez (Feb 8 2003 12:00AM) Terri Duffy (Feb 24 2002 12:00AM) Roberto Tamburello (Dec 5 2001 12:00AM) Rob Walters (Dec 29 2001 12:00AM) … |
[!NOTE]
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.
Article and their tags are separated into different tables. Developer wants to return one row per each article with all associated tags. Using following query:
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 is the result set.
articleId | title | tags |
---|---|---|
172 | Polls indicate close election results | politics,polls,city council |
176 | New highway expected to reduce congestion | NULL |
177 | Dogs continue to be more popular than cats | polls,animals |
The following query finds the email addresses of employees and groups them by towns:
Here is the result set.
town | emails |
---|---|
Seattle | syed0@adventure-works.com;catherine0@adventure-works.com;kim2@adventure-works.com |
LA | sam1@adventure-works.com;hazem0@adventure-works.com |
Emails returned in the emails column can be directly used to send emails to group of people working in some particular towns.
Similar to previous example, the following query finds the email addresses of employees, groups them by town, and sorts the emails alphabetically:
SELECT town,
STRING_AGG (email, ';') WITHIN GROUP (ORDER BY email ASC) AS emails
FROM dbo.Employee
GROUP BY town;
Here is the result set.
town | emails |
---|---|
Seattle | catherine0@adventure-works.com;kim2@adventure-works.com;syed0@adventure-works.com |
LA | hazem0@adventure-works.com;sam1@adventure-works.com |
CONCAT (Transact-SQL)
CONCAT_WS (Transact-SQL)
FORMATMESSAGE (Transact-SQL)
QUOTENAME (Transact-SQL)
REPLACE (Transact-SQL)
REVERSE (Transact-SQL)
STRING_ESCAPE (Transact-SQL)
STUFF (Transact-SQL)
TRANSLATE (Transact-SQL)
Aggregate Functions (Transact-SQL)
String Functions (Transact-SQL)