How to Aggregate String Fields in SQL Server Using a LISTAGG Equivalent?

Using the STRING_AGG function to aggregate string fields in SQL Server

Question: How to efficiently aggregate string fields in a SQL Server query, similar to the LISTAGG function in other database systems?

Solution:

SQL Server provides the STRING_AGG function as an equivalent replacement for the LISTAGG function. Its syntax is as follows:

<code class="language-sql">STRING_AGG ( expression, separator ) WITHIN GROUP ( ORDER BY order_expression [ ASC | DESC ] )</code>

• expression: String expression to aggregate.
• separator: Character or string used to separate aggregate strings.
• order_expression: Expression used to sort aggregate strings. ASC means ascending order, DESC means descending order.

Example:

Suppose there is a table named MyTable containing two fields FieldA and FieldB. The following query uses the STRING_AGG function to aggregate the values ​​in FieldB into a comma-separated string, grouped by FieldA:

<code class="language-sql">SELECT FieldA, STRING_AGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS AggregatedFieldB
FROM MyTable
GROUP BY FieldA
ORDER BY FieldA;</code>

Expected results:

This query will return results similar to the following format:

<code>FieldA | AggregatedFieldB
-------|-----------------
1       | Value1, Value2, Value3
2       | Value4, Value5</code>

This shows that the STRING_AGG function successfully concatenates the FieldA values ​​in each FieldB group into a single string. Note that the values ​​in the resulting string are ordered according to the ORDER BY FieldB clause.

By using the STRING_AGG function, you can easily implement aggregation of string fields in SQL Server, simplifying data processing and report generation.

The above is the detailed content of How to Aggregate String Fields in SQL Server Using a LISTAGG Equivalent?. For more information, please follow other related articles on the PHP Chinese website!