With more and more data being exchanged over APIs, generating comma-separated strings are becoming a much more common requirement.

A few years ago, I wrote about two different ways to generate comma-separated strings. The most common one I find to be in use when generating comma-separated values from a table is the intermediate conversion of XML. This however, is a very costly mechanism and can potentially take minutes for the query to run depending upon the amount of data involved.

SQL Server 2017 brings a new aggregate function that can be used to generate comma-separated values extremely fast. The function is STRING_AGG().

Here’s a sample of it’s usage:

 --WARNING: THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
 --         WARRANTY.
 --         FOR DEMONSTRATION PURPOSES ONLY       
 --Step 01: Generate Temp table to store source data
 DECLARE @NamesTable TABLE ([Id]   INT, 
                            [Name] NVARCHAR(50)
                           );
 --Step 02: Generate test data
 INSERT INTO @NamesTable
 VALUES (1, 'A'),
        (2, 'D'),
        (2, 'C'),
        (3, 'E'),
        (3, 'H'),
        (3, 'G');
 --Step 03: Using STRING_AGG to generate comma-separated strings
 SELECT STRING_AGG(tbl.Name, ',') AS [CommaSeparatedString]
 FROM @NamesTable AS tbl;
 GO
 /RESULTS**
 CommaSeparatedString
 A,D,C,E,H,G
 */

Advantages of STRING_AGG:

• Can be used just like any other aggregate function in a query
• Can work with any user supplied separator – doesn’t necessarily have to be a comma
• No manual step required – Separators are not added at the end of the concatenated string
• STRING_AGG() is significantly faster than using XML based methods
• Can be used with any compatibility level as long as the version is SQL Server 2017 (or higher) and Azure SQL database

Here’s an example of how STRING_AGG can be used with any separator:

 --WARNING: THIS SCRIPT IS PROVIDED AS-IS AND WITHOUT
 --         WARRANTY.
 --         FOR DEMONSTRATION PURPOSES ONLY       
 --Step 01: Generate Temp table to store source data
 DECLARE @NamesTable TABLE ([Id]   INT, 
                            [Name] NVARCHAR(50)
                           );
 --Step 02: Generate test data
 INSERT INTO @NamesTable
 VALUES (1, 'A'),
        (2, 'D'),
        (2, 'C'),
        (3, 'E'),
        (3, 'H'),
        (3, 'G');
 --Step 03: Using STRING_AGG to generate comma-separated strings
 SELECT STRING_AGG(tbl.Name, '-*-') AS [CustomSeparatorString]
 FROM @NamesTable AS tbl;
 GO
 /RESULTS**
 CustomSeparatorString
 A--D--C--E--H--G /

A minor challenge

As with every new feature, there may be a small usability challenge with STRING_AGG. One cannot use keywords like DISTINCT to ensure that only distinct values are used for generating the comma-separated string. There is however a Azure feedback item open where you can exercise your vote if you feel this feature is useful.

Further Reading

• Different ways to generate a comma-separated string from a table [Blog Link]
• STRING_AGG() Aggregate Function [MSDN BOL]

Until we meet next time,

Be courteous. Drive responsibly.