How to Perform List Aggregation in SQL Server?

Detailed explanation of SQL Server list aggregation

In relational database operations, data aggregation is a key step in integrating multiple record information into meaningful summaries. List aggregation is one such aggregation method that concatenates multiple values ​​into a single separated list.

SQL Server itself does not directly provide Oracle-like LISTAGG function. However, the same functionality can be achieved through several techniques.

Method 1: STRING_AGG (SQL Server 2017 and above)

For SQL Server 2017 and later, the STRING_AGG function provides a neat solution for list aggregation:

SELECT FieldA
     , STRING_AGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs
  FROM TableName
 GROUP BY FieldA
 ORDER BY FieldA;

Method 2: Recursive CTE (SQL Server 2016 and below)

In earlier versions of SQL Server, you could use recursive common table expressions (CTEs) to implement list aggregations:

  WITH CTE_TableName AS (
       SELECT FieldA, FieldB
         FROM TableName)
SELECT t0.FieldA
     , STUFF((
       SELECT ',' + t1.FieldB
         FROM CTE_TableName t1
        WHERE t1.FieldA = t0.FieldA
        ORDER BY t1.FieldB
          FOR XML PATH('')), 1, LEN(','), '') AS FieldBs
  FROM CTE_TableName t0
 GROUP BY t0.FieldA
 ORDER BY FieldA;

Compatibility with other databases

For database systems other than SQL Server, the following alternatives are available:

• MySQL: GROUP_CONCAT
• Oracle and DB2: LISTAGG
• PostgreSQL: STRING_AGG

Summary

Although SQL Server does not have a native LISTAGG function, there are many ways to achieve similar functionality. Depending on the version of SQL Server you are using, these methods provide flexible options for aggregating string data into a delimited list.

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