Skip to content

Latest commit

 

History

History
277 lines (207 loc) · 10 KB

string-agg-transact-sql.md

File metadata and controls

277 lines (207 loc) · 10 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
STRING_AGG (Transact-SQL)
STRING_AGG concatenates the values of string expressions and places separator values between them."
MikeRayMSFT
mikeray
randolphwest
01/16/2025
sql
t-sql
reference
STRING_AGG
STRING_AGG_TSQL
STRING_AGG function
TSQL
=azuresqldb-current || =azure-sqldw-latest || >=sql-server-2017 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

STRING_AGG (Transact-SQL)

[!INCLUDE sqlserver2017-asdb-asdbmi-asa-fabricse-fabricdw]

Concatenates the values of string expressions and places separator values between them. The separator isn't added at the end of string.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

STRING_AGG ( expression , separator ) [ <order_clause> ]

<order_clause> ::=
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

Arguments

expression

An expression of any type. Expressions are converted to nvarchar or varchar types during concatenation. Non-string types are converted to nvarchar type.

separator

An expression of nvarchar or varchar type that is used as separator for concatenated strings. It can be literal or variable.

<order_clause>

Optionally specify order of concatenated results using WITHIN GROUP clause:

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_list> 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), the result type is the same as the 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)

Remarks

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.

If the input expression is type varchar, the separator can't be type nvarchar.

Null values are ignored and the corresponding separator isn't 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.

Note

<order_clause> is available with database compatibility level 110 and above.

Examples

[!INCLUDE article-uses-adventureworks]

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;
GO

SELECT STRING_AGG(CONVERT (NVARCHAR (MAX), FirstName), CHAR(13)) AS csv
FROM Person.Person;
GO

[!INCLUDE ssResult_md]

csv
-----------
Syed
Catherine
Kim
Kim
Kim
Hazem
...

NULL values found in name cells aren't returned in the result.

Note

If using the [!INCLUDE ssManStudioFull] Query Editor, the Results to Grid option can't 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;
GO

SELECT STRING_AGG(CONVERT (NVARCHAR (MAX), ISNULL(FirstName, 'N/A')), ',') AS csv
FROM Person.Person;
GO

Here's a trimmed result set.

csv
-----
Syed,Catherine,Kim,Kim,Kim,Hazem,Sam,Humberto,Gustavo,Pilar,Pilar, ...

C. Generate comma-separated values

USE AdventureWorks2022;
GO

SELECT STRING_AGG(CONVERT (NVARCHAR (MAX), CONCAT(FirstName, ' ', LastName, '(', ModifiedDate, ')')), CHAR(13)) AS names
FROM Person.Person;
GO

Here's a trimmed 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 can't 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 OUTER JOIN dbo.ArticleTag AS t
         ON a.ArticleId = t.ArticleId
GROUP BY a.articleId, title;
GO

[!INCLUDE ssResult_md]

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

Note

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;
GO

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;
GO

[!INCLUDE ssResult_md]

Note

Results are shown trimmed.

City emails
Ballard [email protected];[email protected];[email protected];...
Baltimore [email protected]
Barstow [email protected]
Basingstoke Hants [email protected];[email protected]
Baytown [email protected]
Beaverton [email protected];[email protected];[email protected];...
Bell Gardens [email protected]
Bellevue [email protected];[email protected];[email protected];...
Bellflower [email protected];[email protected];[email protected];...
Bellingham [email protected];[email protected];[email protected];...

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;
GO

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;
GO

[!INCLUDE ssResult_md]

Note

Results are shown trimmed.

City Emails
Barstow [email protected]
Basingstoke Hants [email protected];[email protected]
Braintree [email protected]
Bell Gardens [email protected]
Byron [email protected]
Bordeaux [email protected]
Carnation [email protected];[email protected];[email protected];...
Boulogne-Billancourt [email protected];[email protected];[email protected];...
Berkshire [email protected];[email protected];[email protected];...
Berks [email protected];[email protected];[email protected];...

Related content