SQL Server STRING_AGG: The Ultimate Guide to Concatenating Strings in SQL

The STRING_AGG function in SQL Server is a powerful aggregate function that simplifies the process of concatenating strings from multiple rows into a single string. Introduced in SQL Server 2017, STRING_AGG offers a more efficient and readable alternative to older methods of string aggregation. This comprehensive guide will explore the syntax, usage, and practical examples of STRING_AGG, providing you with everything you need to master string concatenation in SQL Server.

Understanding STRING_AGG in SQL Server

STRING_AGG is designed to aggregate string expressions from different rows, placing a specified separator between each value. It’s particularly useful when you need to create comma-separated lists, custom delimited strings, or any consolidated string output from relational data.

Syntax of STRING_AGG

The basic syntax for the STRING_AGG function is as follows:

STRING_AGG ( expression , separator ) [ <order_clause> ]

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

Let’s break down each component:

expression: This is the string expression that you want to concatenate. It can be a column name, a literal string, or any expression that evaluates to a string. If the expression is not already a string type, it will be implicitly converted to nvarchar or varchar during the concatenation process.
separator: This is the string that will be placed between each concatenated value. It can be a literal string like a comma (,), a space (`), a newline character (CHAR(13)`), or a variable containing a separator string.
WITHIN GROUP (ORDER BY ...): This optional clause allows you to specify the order in which the expressions are concatenated. Using ORDER BY within the WITHIN GROUP clause ensures that the strings are combined in a predictable and meaningful sequence.

Return Types in STRING_AGG

The return type of STRING_AGG is determined by the input expression. If the expression is a string type (nvarchar, varchar), the output type will match the input type. SQL Server performs automatic conversions as shown in this table:

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)

It’s important to note that if your input expression is varchar, the separator cannot be nvarchar.

Handling NULL Values

STRING_AGG gracefully handles NULL values by ignoring them. This means that if any of the expressions being concatenated are NULL, they will not be included in the final concatenated string, and the separator will not be added for those NULL values. If you need to include a placeholder for NULL values, you can use the ISNULL or COALESCE functions, as demonstrated in the examples below.

Availability

STRING_AGG is available in SQL Server 2017 (14.x) and later versions, Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, SQL analytics endpoint in Microsoft Fabric, and Warehouse in Microsoft Fabric. It is compatible with database compatibility level 110 and above.

Practical Examples of STRING_AGG

Let’s explore several examples to illustrate the versatility of STRING_AGG. We’ll use the AdventureWorks2022 sample database for these examples.

Example A: Creating a Comma-Separated List of Names

This example demonstrates how to generate a simple comma-separated list of first names from the Person.Person table.

USE AdventureWorks2022;
GO
SELECT STRING_AGG(FirstName, ', ') AS CommaSeparatedNames
FROM Person.Person;
GO

This query will return a single row with a column named CommaSeparatedNames containing a string of first names separated by commas and spaces.

Example B: Generating a Newline-Separated List

To create a list where each name appears on a new line, you can use the carriage return character CHAR(13) as the separator.

USE AdventureWorks2022;
GO
SELECT STRING_AGG(FirstName, CHAR(13)) AS NewLineSeparatedNames
FROM Person.Person;
GO

This will output a single string with each first name on a new line. When viewing results in SQL Server Management Studio, ensure you are using “Results to Text” mode to properly visualize newline characters.

Example C: Handling NULLs with ISNULL

If you want to include placeholders for NULL values, you can use the ISNULL function to replace NULL with a specific string, such as ‘N/A’.

USE AdventureWorks2022;
GO
SELECT STRING_AGG(ISNULL(FirstName, 'N/A'), ', ') AS NamesWithNulls
FROM Person.Person;
GO

In this case, if any FirstName values are NULL, they will be replaced with ‘N/A’ in the concatenated string.

Example D: Creating Custom Formatted Strings

STRING_AGG is not limited to simple column values. You can use expressions and functions within the expression argument to create more complex strings.

USE AdventureWorks2022;
GO
SELECT STRING_AGG(CONCAT(FirstName, ' ', LastName, ' (', ModifiedDate, ')'), CHAR(13)) AS FormattedNames
FROM Person.Person;
GO

This example concatenates the first name, last name, and modified date into a formatted string for each person, separated by newlines.

Example E: Aggregating Tags for Articles

Consider a scenario where you have Articles and ArticleTags tables. You can use STRING_AGG to retrieve all tags associated with each article in a single row.

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

This query uses a LEFT OUTER JOIN to combine articles and their tags. The STRING_AGG(tag, ', ') function then concatenates the tags for each article, separated by commas. The GROUP BY clause is essential here because STRING_AGG is an aggregate function, and we need to group the results by articleId and title to get tags per article.

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

Example F: Generating Email Lists Grouped by City

STRING_AGG can be used to create email lists grouped by categories, such as cities.

USE AdventureWorks2022;
GO
SELECT TOP 10 City, STRING_AGG(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

This query joins tables to retrieve email addresses and city information. It then uses STRING_AGG(EmailAddress, '; ') to create a semicolon-separated list of emails for each city, grouped by City.

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]; …

Example G: Ordered String Aggregation

To control the order of concatenated strings, use the WITHIN GROUP (ORDER BY ...) clause.

USE AdventureWorks2022;
GO
SELECT TOP 10 City,
       STRING_AGG(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

This example is similar to the previous one, but it adds WITHIN GROUP (ORDER BY EmailAddress ASC) to sort the email addresses alphabetically within each city’s list.

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]; …

Conclusion

STRING_AGG is a valuable addition to the SQL Server aggregate function set, offering a clean and efficient way to concatenate strings. Whether you need to create simple lists, formatted strings, or aggregate data for reporting and analysis, STRING_AGG provides the flexibility and performance you need. By understanding its syntax, return types, and options like WITHIN GROUP (ORDER BY ...), you can effectively utilize STRING_AGG to enhance your SQL queries and data manipulation tasks.