How Can I Concatenate Values in SQL Server Before 2017?

String Concatenation in Pre-2017 SQL Server

SQL Server 2017 introduced the convenient STRING_AGG function for concatenating values. For versions prior to 2017, a different technique is required.

SQL Server 2014 and Earlier

In SQL Server 2014 (and earlier versions), you can effectively concatenate values using a combination of STUFF() and FOR XML PATH():

SELECT STUFF((SELECT ',' + CAST(t.id AS VARCHAR(MAX))
              FROM YourTable t
              FOR XML PATH('')), 1, 1, '');

Replace YourTable with the actual name of your table and id with the column you want to concatenate.

The FOR XML PATH('') clause cleverly converts the result set into an XML string, effectively concatenating the values. The STUFF() function then elegantly removes the leading comma added by the ',' ... part of the query. This approach provides a robust solution for string aggregation in older SQL Server instances.

The above is the detailed content of How Can I Concatenate Values in SQL Server Before 2017?. For more information, please follow other related articles on the PHP Chinese website!