如何用SQL实现按样本合并多行数据并聚合列值？

我有一张汇总实验室分析样本数据的表格，部分实验室通过不同文件发送不同元素的数据，导致同一样本的数据分散在多行中。需要编写SQL查询，将同一样本各列的取值合并到单独一行。

原始数据 #

Batch Source_File Value Value 2
SAMPLE A   1        A        150     null
SAMPLE A   1        B        null    100
SAMPLE B   2        C        null    300
SAMPLE B   2        D        100     null

期望输出 #

Batch Source_File Value Value 2
SAMPLE A   1       A,B      150   100
SAMPLE B   2       C,D      100   300

针对不同数据库的实现方案 #

MySQL/MariaDB #

使用GROUP_CONCAT合并Source_File，通过MAX()提取非空的Value和Value 2（同组内仅一行有有效值，其余为null，MAX()会忽略null）：

SELECT 
    `Sample`,
    Batch,
    GROUP_CONCAT(Source_File ORDER BY Source_File SEPARATOR ',') AS Source_File,
    MAX(Value) AS Value,
    MAX(`Value 2`) AS `Value 2`
FROM your_table_name
GROUP BY `Sample`, Batch;

PostgreSQL #

使用STRING_AGG进行字符串聚合，配合MAX()提取有效值：

SELECT 
    "Sample",
    Batch,
    STRING_AGG(Source_File, ',' ORDER BY Source_File) AS Source_File,
    MAX(Value) AS Value,
    MAX("Value 2") AS "Value 2"
FROM your_table_name
GROUP BY "Sample", Batch;

SQL Server #

2017及以上版本（支持STRING_AGG）

SELECT 
    [Sample],
    Batch,
    STRING_AGG(Source_File, ',' ) WITHIN GROUP (ORDER BY Source_File) AS Source_File,
    MAX(Value) AS Value,
    MAX([Value 2]) AS [Value 2]
FROM your_table_name
GROUP BY [Sample], Batch;

2016及以下版本（用STUFF+FOR XML PATH实现字符串聚合）

SELECT 
    DISTINCT [Sample],
    Batch,
    STUFF((
        SELECT ',' + Source_File 
        FROM your_table_name t2 
        WHERE t2.[Sample] = t1.[Sample] AND t2.Batch = t1.Batch
        ORDER BY Source_File
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS Source_File,
    MAX(Value) OVER (PARTITION BY [Sample], Batch) AS Value,
    MAX([Value 2]) OVER (PARTITION BY [Sample], Batch) AS [Value 2]
FROM your_table_name t1;

Oracle #

使用LISTAGG完成字符串聚合：

SELECT 
    "Sample",
    Batch,
    LISTAGG(Source_File, ',' ) WITHIN GROUP (ORDER BY Source_File) AS Source_File,
    MAX(Value) AS Value,
    MAX("Value 2") AS "Value 2"
FROM your_table_name
GROUP BY "Sample", Batch;

核心原理 #

通过Sample和Batch分组，确保同一样本的所有行被聚合到一组：

1. 用对应数据库的字符串聚合函数，将同组的Source_File合并为逗号分隔的字符串；
2. 用MAX()（或MIN()）提取Value和Value 2的有效值，因为同组内仅一行有非空值，聚合函数会自动忽略null。

内容的提问来源于stack exchange，提问作者Túlio H. R.R.