How Can I Replicate SQL Server's `string_agg` Function in Versions Before 2017?

Emulating string_agg in Pre-2017 SQL Server

SQL Server's string_agg function, introduced in 2017, simplifies string concatenation. However, for older versions, we need alternative approaches. This article demonstrates a method to achieve similar results.

The goal is to replicate this query's functionality:

<code class="language-sql">SELECT STRING_AGG(t.id, ',') AS id
FROM Table t;</code>

This concatenates 'id' column values from the 'Table' table, using a comma as a separator.

Pre-2017 SQL Server lacks string_agg. The solution utilizes the FOR XML PATH method:

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

This cleverly constructs a comma-separated string. FOR XML PATH('') generates an XML representation, effectively creating the comma-separated list. STUFF() then removes the leading comma. This effectively mimics the string_agg behavior.

This workaround provides a practical solution for string concatenation in older SQL Server versions, mirroring the functionality of the newer string_agg function.

The above is the detailed content of How Can I Replicate SQL Server's `string_agg` Function in Versions Before 2017?. For more information, please follow other related articles on the PHP Chinese website!