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 PATH
This 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
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.
Please note that, The STRING_AGG() ignores NULL and it does not add the separator for NULL when performing concatenation.
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
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!
2 thoughts on “How to build comma separated string in SQL Server”