FOR XML PATH vs. STRING_AGG: Row Wrangling with Comma-Separated Lists in SQL Server

This method uses two built-in functions: FOR XML PATH and STUFF.

Explanation:

• FOR XML PATH: Converts the result set into an XML document with each row as an element.
• STUFF: Extracts the desired column values from the XML document and combines them with a comma separator, removing the unnecessary leading comma.

Example:

SELECT STUFF((
  SELECT ', ' + CAST(Name AS varchar(max))
  FROM YourTable T2
  FOR XML PATH(''), TYPE
), 1, 2, '') AS CommaSeparatedList
FROM YourTable T1
GROUP BY SomeGroupingColumn;

• The inner query selects the Name column, casts it to varchar(max), and prepends a comma and space (', ').
• FOR XML PATH('') converts the result set into an XML string.
• The outer query uses STUFF to:
  • Take the second parameter (1) as the starting position (removing the leading comma).
  • Take the third parameter (2) as the number of characters to remove (the leading comma).
  • Concatenate the remaining string with an empty string ('') as the fourth parameter.
• This STUFF function ensures only the actual values are included without the initial comma.
• The query groups the data by SomeGroupingColumn to create separate comma-separated lists for each group.

STRING_AGG (introduced in SQL Server 2005 Service Pack 3):

This method uses the STRING_AGG function, available in Service Pack 3 and later versions.

STRING_AGG efficiently aggregates string values into a single string with a specified delimiter.

SELECT STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name) AS CommaSeparatedList
FROM YourTable
GROUP BY SomeGroupingColumn;

• STRING_AGG:
  • First argument: The column to aggregate (Name).
  • Second argument: The delimiter (comma and space).
• WITHIN GROUP (ORDER BY Name):
  • Specifies the aggregation within each group.
  • Orders the values alphabetically before concatenation.

Related Issues and Solutions:

• Handling NULL values: Both methods treat NULL values as empty strings in the list. If you want to skip NULL values, use ISNULL or COALESCE functions in the inner query before concatenation.
• Limiting the list length: While SQL Server 2005 doesn't have a built-in function, you can use substring operations on the final result to limit the list length if necessary. However, it's generally recommended to avoid excessively long comma-separated lists and consider alternative data structures or approaches.