SQL Server按Date与ID分组拼接非重复字段的SQL查询需求

原始数据 #

Date            Id      Ethnicity   Gender      Age 
-----------------------------------------------------
2021-03-25      001     NULL        NULL        39
2021-03-25      001     Hispanic    Male        39
2022-02-01      002     NULL        NULL        NULL
2022-02-01      002     Unknown     Male        27
2022-02-01      002     Hispanic    Male        27
2022-02-01      002     NULL        Male        NULL
2022-02-02      003     NULL        Male        Null
2022-02-02      003     White       Male        Null

目标结果 #

2021-03-25          001     Hispanic, Male, 39
2022-02-01          002     Hispanic, Male, 27
2022-02-02          003     White, Male

问题说明 #

直接使用STRING_AGG()会出现重复内容（比如ID 002会得到Unknown, Male, 27, Hispanic, Male,27, Male），需要按Date和Id分组，将Ethnicity、Gender、Age字段拼接为无重复的字符串，同时忽略NULL值。

解决方案 #

核心思路是先对每个分组内的字段值分别去重，再合并拼接，以下两种方法均可实现需求：

方法一：CTE拆分去重后合并 #

先提取每个分组下非空且唯一的字段值，再将不同字段的结果统一到一列后拼接：

WITH DistinctValues AS (
    SELECT 
        Date,
        Id,
        DISTINCT CASE WHEN Ethnicity IS NOT NULL THEN Ethnicity END AS Ethnicity,
        DISTINCT CASE WHEN Gender IS NOT NULL THEN Gender END AS Gender,
        DISTINCT CASE WHEN Age IS NOT NULL THEN CAST(Age AS VARCHAR(10)) END AS Age
    FROM YourTableName
)
SELECT 
    Date,
    Id,
    STRING_AGG(Value, ', ') AS CombinedResult
FROM (
    SELECT Date, Id, Ethnicity AS Value FROM DistinctValues WHERE Ethnicity IS NOT NULL
    UNION ALL
    SELECT Date, Id, Gender AS Value FROM DistinctValues WHERE Gender IS NOT NULL
    UNION ALL
    SELECT Date, Id, Age AS Value FROM DistinctValues WHERE Age IS NOT NULL
) AS Unpivoted
GROUP BY Date, Id
ORDER BY Date, Id;

方法二：子查询过滤去重后直接拼接 #

过滤掉全空行，将非空字段转为统一字符串后，用DISTINCT去重再拼接：

SELECT 
    Date,
    Id,
    STRING_AGG(DISTINCT Value, ', ') AS CombinedResult
FROM (
    SELECT 
        Date,
        Id,
        CASE 
            WHEN Ethnicity IS NOT NULL THEN Ethnicity
            WHEN Gender IS NOT NULL THEN Gender
            WHEN Age IS NOT NULL THEN CAST(Age AS VARCHAR(10))
        END AS Value
    FROM YourTableName
    WHERE Ethnicity IS NOT NULL OR Gender IS NOT NULL OR Age IS NOT NULL
) AS FilteredValues
WHERE Value IS NOT NULL
GROUP BY Date, Id
ORDER BY Date, Id;

注意事项 #

• Age为数值类型，需转换为字符串才能与其他字段拼接；
• 替换YourTableName为实际表名；
• 两种方法均通过DISTINCT确保值唯一，用过滤条件排除NULL。

内容的提问来源于stack exchange，提问作者zbx888