How Do Different SQL Databases Implement String Aggregation (LISTAGG)?

String Aggregation in Various SQL Databases

This article demonstrates how to concatenate multiple string values into a single string using different SQL databases. The goal is to aggregate a string field.

Oracle and DB2:

Both Oracle and DB2 offer the built-in LISTAGG function. The syntax is straightforward:

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

PostgreSQL:

PostgreSQL utilizes the STRING_AGG function:

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

SQL Server:

SQL Server 2017 and later versions also support STRING_AGG. For older versions (pre-2017), a more complex approach using STUFF and FOR XML PATH is necessary:

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;

SQLite:

SQLite's GROUP_CONCAT function can perform string aggregation. Ordering requires a CTE or subquery:

With Ordering:

WITH CTE_TableName AS (
    SELECT FieldA, FieldB
    FROM TableName
    ORDER BY FieldA, FieldB
)
SELECT FieldA,
       GROUP_CONCAT(FieldB, ',') AS FieldBs
FROM CTE_TableName
GROUP BY FieldA
ORDER BY FieldA;

Without Ordering:

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

The above is the detailed content of How Do Different SQL Databases Implement String Aggregation (LISTAGG)?. For more information, please follow other related articles on the PHP Chinese website!