How to Perform String Aggregation in SQL Server Before 2017?

String aggregation in versions prior to SQL Server 2017

In various scenarios, there is a need for string aggregation. Databases like Postgresql provide simple string_agg functions to meet this need. However, in SQL Server versions prior to 2017, this function is missing and users need to find an alternative.

One solution is to take advantage of the XML PATH option. The following query demonstrates how to apply this approach in SQL Server 2014:

<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 only purpose of the stuff() function is to remove the leading comma. The actual work of string aggregation is performed by the for xml path expression. The result is a comma-separated list of ids generated from table t.

This approach may not be as intuitive as using the string_agg function, but it provides a viable alternative for string aggregation in versions of SQL Server that do not have the built-in function.

The above is the detailed content of How to Perform String Aggregation in SQL Server Before 2017?. For more information, please follow other related articles on the PHP Chinese website!