How Can I Aggregate Strings in SQL Server Like Oracle's LISTAGG?

List Aggregation in SQL Server

SQL Server provides a powerful set of functions for data aggregation, including the ability to concatenate values ​​from multiple rows into a single comma-separated string. Mastering this aggregation method is critical for a variety of data aggregation and presentation tasks.

To achieve the same functionality as Oracle's LISTAGG function in SQL Server, you can use the STRING_AGG function. Introduced in SQL Server 2017, this function allows concatenating strings within a group.

Example

Consider the following dataset:

字段 A	字段 B
1	A
1	B
2	A

Query:

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

Result:

字段 A	FieldBs
1	A, B
2	A

The STRING_AGG function with the WITHIN GROUP clause ensures that values ​​are concatenated within each group grouped by FieldA. The ORDER BY clause in an aggregate specifies the order in which values ​​should be listed.

For SQL Server versions prior to 2017, you can use a combination of CTE (common table expression) and STUFF functions to achieve the same result. However, for simplicity and compatibility with newer versions, the STRING_AGG function is the recommended solution.

The above is the detailed content of How Can I Aggregate Strings in SQL Server Like Oracle's LISTAGG?. For more information, please follow other related articles on the PHP Chinese website!