SQL Server 2019如何聚合/拼接字符串且去除重复值

你的问题出在STRING_AGG直接聚合了分组内的所有值（包括重复项），而开头的DISTINCT对聚合后的字符串去重无效——因为GROUP BY已经保证了material+plant组合的唯一性。以下分两种场景给出解决方案：

场景1：使用SQL Server 2022及以上版本 #

SQL Server 2022开始支持STRING_AGG中直接使用DISTINCT参数，可直接在聚合时完成去重：

SELECT 
    material, 
    plant, 
    STRING_AGG(DISTINCT exceptions, ';') AS exceptions,
    STRING_AGG(DISTINCT preferential_country, ';') AS preferential_country
FROM grs
GROUP BY material, plant

场景2：使用SQL Server 2022以下版本 #

旧版本不支持STRING_AGG(DISTINCT)，需要先通过子查询/CTE对分组内的字段值去重，再进行聚合：

WITH DistinctGrs AS (
    -- 提取每个material+plant下唯一的(exceptions, preferential_country)组合
    SELECT DISTINCT 
        material, 
        plant, 
        exceptions, 
        preferential_country
    FROM grs
)
SELECT 
    material, 
    plant, 
    STRING_AGG(exceptions, ';') AS exceptions,
    STRING_AGG(preferential_country, ';') AS preferential_country
FROM DistinctGrs
GROUP BY material, plant

如果需要对exceptions和preferential_country独立去重（不考虑两者的对应关系），可以用以下方式：

-- 先获取唯一的material+plant分组
WITH UniqueGroups AS (
    SELECT DISTINCT material, plant FROM grs
),
-- 聚合去重后的exceptions
ExceptionAgg AS (
    SELECT 
        material, 
        plant, 
        STRING_AGG(exceptions, ';') AS exceptions_list
    FROM (
        SELECT DISTINCT material, plant, exceptions FROM grs
    ) sub
    GROUP BY material, plant
),
-- 聚合去重后的preferential_country
CountryAgg AS (
    SELECT 
        material, 
        plant, 
        STRING_AGG(preferential_country, ';') AS country_list
    FROM (
        SELECT DISTINCT material, plant, preferential_country FROM grs
    ) sub
    GROUP BY material, plant
)
-- 关联结果
SELECT 
    ug.material, 
    ug.plant, 
    ea.exceptions_list AS exceptions,
    ca.country_list AS preferential_country
FROM UniqueGroups ug
LEFT JOIN ExceptionAgg ea ON ug.material = ea.material AND ug.plant = ea.plant
LEFT JOIN CountryAgg ca ON ug.material = ca.material AND ug.plant = ca.plant

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