Building a comma separated string is a very frequent&common requirement for SQL Server developers. Developers find different ways to achieve this like CLR/XML PATH/Co-related queries etc. With this post, we are going to see two options that are widely used XML PATH & string_AGG and a quick look at its performance comparison.
— Using STUFF & XML PATHThis is the most widely used method to build the comma separated string. In my personal experience, I observed many performance issues related to this method for a large set of data.
Drop Table if exists BuildCommaSeparatedString
Create Table BuildCommaSeparatedString(SessionID int, CourseID varchar(100))
Insert into BuildCommaSeparatedString Values
(1,'English'),(1,'Maths'),(1,'Accountancy'),(2,'History'),(2,'Biology')
SELECT SessionID,STUFF((SELECT ',' + CourseID FROM BuildCommaSeparatedString EE
WHERE EE.SessionID=E.SessionID
ORDER BY CourseID -- To order the Courses in order
FOR XML PATH('')), 1, 1, '') AS 'CommaseparatedString'
FROM BuildCommaSeparatedString E
GROUP BY E.SessionID
— Using string_AGG in SQL Server 2017
With SQL Server 2017 release, there is a better way to do this using string_agg built-in function. The STRING_AGG() is an aggregate function that concatenates rows of strings into a single string, separated by a specified separator.
STRING_AGG ( input_string, separator ) [ order_clause ]
input_string - is the column name of any type that can convert to a comma separated string
separator - is the separator caharacter like , or ; etc
[ order_clause ] - specifies the sort order of concatenated results using WITHIN GROUP clause
WITHIN GROUP ( ORDER BY expression [ ASC | DESC ] )
Drop Table if exists BuildCommaSeparatedString
Create Table BuildCommaSeparatedString(SessionID int, CourseID varchar(100))
Insert into BuildCommaSeparatedString Values
(1,'English'),(1,'Maths'),(1,'Accountancy'),(2,'History'),(2,'Biology')
Select SessionID, STRING_AGG(CourseID,',') From BuildCommaSeparatedString Group by SessionID
--To build the comma separated string in an order way
Select SessionID, STRING_AGG(CourseID,',') within group (Order by CourseID asc)
From BuildCommaSeparatedString Group by SessionID
A quick look on Performance benefit
The below snapshot clearly indicates that the performance benefit of string_AGG built-in function over XML PATH approach.
Conclusion
If you are in SQL Server 2017 – use built-in function string_AGG instead of any other method. I have seen this outperform many times than a custom built code. If you do not agree or have any different experience, please share the specific case with an example for the benefits of readers. Thanks in advance!
SQLZealots 