How Can I Achieve String Aggregation in Older SQL Server Versions (Pre-2017)?

How to use String_agg in older versions of SQL Server

The

string_agg function is a powerful tool for concatenating multiple rows into a single string and is widely used in various SQL databases such as PostgreSQL and SQL Server. However, the string_agg function is not natively supported in SQL Server versions prior to 2017.

Solution for SQL Server 2014:

To achieve similar functionality in SQL Server 2014, you can use the following query:

<code class="language-sql">SELECT STUFF((SELECT ',' + CAST(t.id AS VARCHAR(MAX))
              FROM tabel t
              FOR XML PATH('')
             ), 1, 1, '');</code>

In this query:

The
• FOR XML PATH('') function generates a comma separated list of id values.
• STUFF function removes the initial comma from the result.
• CAST(t.id AS VARCHAR(MAX)) Ensures that all id values ​​are converted to strings regardless of their original data type.

This method effectively emulates the behavior of string_agg, allowing you to concatenate multiple id values ​​into a single comma-separated string.

The above is the detailed content of How Can I Achieve String Aggregation in Older SQL Server Versions (Pre-2017)?. For more information, please follow other related articles on the PHP Chinese website!