In modern database management, the ability to aggregate data and present it in a user-friendly format is crucial. SQL Server, a leading database management system, offers a powerful function called STRING_AGG to address this need. Introduced in SQL Server 2017, STRING_AGG simplifies the process of concatenating strings from multiple rows into a single, coherent string, making data presentation and reporting more efficient and readable. This article delves into the intricacies of STRING_AGG, providing a comprehensive guide for database professionals looking to leverage its capabilities.
Understanding the Syntax and Arguments of STRING_AGG
The STRING_AGG function in SQL Server is designed to aggregate string expressions by placing a specified separator between them. The fundamental syntax 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 value that you want to concatenate. It can be any expression that evaluates to a string type or can be implicitly converted to a string type. Common data types includenvarcharandvarchar. Non-string types are automatically converted tonvarchar.separator: This is the string that will be inserted between each concatenated value. It can be a literal string, a variable, or an expression that evaluates tonvarcharorvarchar. Common separators include commas, semicolons, spaces, or line breaks.WITHIN GROUP (ORDER BY ...): This optional clause specifies the order in which the expressions from different rows are concatenated. If you need the aggregated string to have a specific order, you can useORDER BYwithin theWITHIN GROUPclause to define the sorting criteria.
Return Types in STRING_AGG
The return type of STRING_AGG is determined by the input expression. If the input expression is a string type, the function returns the same string type. SQL Server handles automatic conversions as outlined in the table below:
| Input Expression Type | Result Type |
|---|---|
| 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) |
This automatic conversion ensures flexibility when dealing with different data types within your database.
Key Considerations and Behavior of STRING_AGG
When working with STRING_AGG, it’s important to understand its behavior in specific scenarios:
- Null Value Handling:
STRING_AGGintelligently ignores null values. This means that if any of the expressions in the rows are null, they will not be included in the concatenated string, and the separator will not be added in place of the null value. If you need to represent null values in the output, you can use functions likeISNULLorCOALESCEto replace nulls with a placeholder before usingSTRING_AGG. - Data Type Conversion: SQL Server performs implicit conversion of expressions to string types before concatenation. This follows standard data type conversion rules in SQL Server. For explicit control over conversions, you can use
CASTorCONVERTfunctions. - Compatibility Level:
STRING_AGGis available in all compatibility levels for SQL Server 2017 and later, as well as Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics, SQL analytics endpoint in Microsoft Fabric, and Warehouse in Microsoft Fabric. For older compatibility levels, consider upgrading to leverage this functionality.
Practical Examples of STRING_AGG in Action
To illustrate the power and versatility of STRING_AGG, let’s explore several practical examples using the AdventureWorks2022 sample database.
A. Generating a List of Names Separated by New Lines
This example demonstrates how to create a list of first names, with each name on a new line.
USE AdventureWorks2022;
GO
SELECT STRING_AGG(CONVERT(NVARCHAR(MAX), FirstName), CHAR(13)) AS csv
FROM Person.Person;
GOThis query will produce a single cell result, with each first name from the Person.Person table separated by a carriage return (CHAR(13)).
csv
-----------
Syed
Catherine
Kim
Kim
Kim
Hazem
...Alt text: Topic link icon indicating further information about list generation with carriage returns.
Alt text for image: Topic link icon indicating further information about list generation with carriage returns.
B. Generating a Comma-Separated List of Names, Handling NULLs
In this example, we handle potential NULL values in the FirstName column by replacing them with ‘N/A’ and then create a comma-separated list.
USE AdventureWorks2022;
GO
SELECT STRING_AGG(CONVERT(NVARCHAR(MAX), ISNULL(FirstName, 'N/A')), ',') AS csv
FROM Person.Person;
GOThis query uses ISNULL to substitute ‘N/A’ for any null first names, ensuring no nulls disrupt the comma-separated list.
csv
-----
Syed,Catherine,Kim,Kim,Kim,Hazem,Sam,Humberto,Gustavo,Pilar,Pilar,...C. Creating Comma-Separated Values with Complex Formatting
This example shows how to concatenate first name, last name, and modification date, formatted within parentheses, separated by line breaks.
USE AdventureWorks2022;
GO
SELECT STRING_AGG(CONVERT(NVARCHAR(MAX), CONCAT(FirstName, ' ', LastName, '(', ModifiedDate, ')')), CHAR(13)) AS names
FROM Person.Person;
GOThis query combines multiple columns into a formatted string for each person, then aggregates these strings with carriage returns.
names
-------
Ken Sánchez (2003-02-08 00:00:00.000)
Terri Duffy (2002-02-24 00:00:00.000)
Roberto Tamburello (2001-12-05 00:00:00.000)
Rob Walters (2001-12-29 00:00:00.000)
...D. Returning News Articles with Related Tags
Consider a scenario with Article and ArticleTag tables. This example demonstrates how to list articles with their associated tags as a comma-separated string.
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;
GOThis query uses STRING_AGG within a GROUP BY clause to aggregate tags for each 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 |
E. Generating a List of Emails per City
This example groups email addresses by city, creating a semicolon-separated list of emails for each 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;
GOThis query efficiently aggregates emails, providing a ready-to-use list for communication purposes.
| 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];… |
F. Generating a Sorted List of Emails per City
Building on the previous example, this query sorts the email addresses alphabetically within each city’s aggregated list.
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;
GOBy adding the WITHIN GROUP (ORDER BY EmailAddress ASC) clause, the emails are now sorted 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];… |
Benefits of Using STRING_AGG
STRING_AGG offers several advantages over older methods of string concatenation in SQL Server:
- Readability and Simplicity: It provides a cleaner and more intuitive syntax compared to using
FOR XML PATH('')or cursor-based approaches, making queries easier to write and understand. - Performance:
STRING_AGGis optimized for performance, often outperforming older methods, especially when dealing with large datasets. - Ordering: The
WITHIN GROUP (ORDER BY ...)clause allows for ordered concatenation, which was more complex to achieve with previous techniques. - Conciseness: It reduces the amount of code needed to perform string aggregation, leading to more maintainable and less error-prone SQL scripts.
Conclusion
The STRING_AGG function in SQL Server is a powerful tool for string aggregation, simplifying data presentation and reporting. By understanding its syntax, return types, and behavior, and by leveraging practical examples, database professionals can effectively use STRING_AGG to enhance their SQL queries. Its efficiency, readability, and flexibility make it an invaluable asset for modern database development and management. Whether you need to create comma-separated lists, generate reports with aggregated data, or format data for application consumption, STRING_AGG is a robust solution for all your string concatenation needs in SQL Server.
