如何在SQL Server中使用STRING_AGG聚合时去除重复值

你的问题核心是当前的STRING_AGG聚合未先对字段值去重，导致重复内容被拼接进去。以下是两种可行解决方案，适配不同版本的SQL Server：

方案1：SQL Server 2022及更高版本（推荐） #

SQL Server 2022开始支持在STRING_AGG中直接使用DISTINCT关键字，写法简洁高效：

WITH CombinedData AS (
    SELECT 
        tmo.SubscriptionId,
        tmo.ResourceGroupName,
        -- 去重后聚合Number
        STRING_AGG(DISTINCT tmo.Number, ',') AS Numbers,
        -- 去重后聚合BusinessOwner
        STRING_AGG(DISTINCT tmo.BusinessOwner, ', ') AS BusinessOwners,
        -- 去重后聚合ITApplicationOwner
        STRING_AGG(DISTINCT tmo.ITApplicationOwner, ', ') AS ITApplicationOwners,
        -- 去重后聚合Parent
        STRING_AGG(DISTINCT tmo.Parent, ', ') AS Parents
    FROM 
        [TRFM_multipleappid-owners] tmo
    GROUP BY 
        tmo.SubscriptionId, 
        tmo.ResourceGroupName
)
SELECT 
    SubscriptionId,
    Numbers,
    BusinessOwners,
    ResourceGroupName,
    ITApplicationOwners,
    Parents
FROM 
    CombinedData;

方案2：SQL Server 2017/2019版本（兼容旧版） #

旧版本SQL Server不支持STRING_AGG直接搭配DISTINCT，需先通过子查询获取去重后的字段值，再进行聚合：

WITH CombinedData AS (
    SELECT 
        tmo.SubscriptionId,
        tmo.ResourceGroupName,
        -- 先去重Number再聚合
        (SELECT STRING_AGG(Number, ',') 
         FROM (SELECT DISTINCT Number FROM [TRFM_multipleappid-owners] 
               WHERE SubscriptionId = tmo.SubscriptionId AND ResourceGroupName = tmo.ResourceGroupName) AS DistinctNumbers) AS Numbers,
        -- 先去重BusinessOwner再聚合
        (SELECT STRING_AGG(BusinessOwner, ', ') 
         FROM (SELECT DISTINCT BusinessOwner FROM [TRFM_multipleappid-owners] 
               WHERE SubscriptionId = tmo.SubscriptionId AND ResourceGroupName = tmo.ResourceGroupName) AS DistinctOwners) AS BusinessOwners,
        -- 先去重ITApplicationOwner再聚合
        (SELECT STRING_AGG(ITApplicationOwner, ', ') 
         FROM (SELECT DISTINCT ITApplicationOwner FROM [TRFM_multipleappid-owners] 
               WHERE SubscriptionId = tmo.SubscriptionId AND ResourceGroupName = tmo.ResourceGroupName) AS DistinctITOwners) AS ITApplicationOwners,
        -- 先去重Parent再聚合
        (SELECT STRING_AGG(Parent, ', ') 
         FROM (SELECT DISTINCT Parent FROM [TRFM_multipleappid-owners] 
               WHERE SubscriptionId = tmo.SubscriptionId AND ResourceGroupName = tmo.ResourceGroupName) AS DistinctParents) AS Parents
    FROM 
        [TRFM_multipleappid-owners] tmo
    GROUP BY 
        tmo.SubscriptionId, 
        tmo.ResourceGroupName
)
SELECT 
    SubscriptionId,
    Numbers,
    BusinessOwners,
    ResourceGroupName,
    ITApplicationOwners,
    Parents
FROM 
    CombinedData;

效果说明 #

两种方案都会输出你期望的结果：

• BusinessOwner字段仅保留John Doe, Maria Johnson（去除重复的John Doe）
• Parent字段仅保留Global Tech Corp (TECH)（去除重复值）
• 其他字段也会自动去除重复的拼接内容

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