SQL Server：按ArtNo合并多行并拼接ArtId为逗号分隔列

你的原SQL存在两个核心问题：

1. 子查询中at1.ArtId != at2.ArtId会排除当前行的ArtId，导致拼接结果缺失部分ID
2. 用原表关联子查询会产生大量重复数据，后续的DISTINCT和GROUP BY无法正确合并行

以下提供两种适配不同SQL Server版本的解决方案，均能实现按ArtNo合并行、拼接ArtId为逗号分隔字符串的需求，单行时直接显示ArtId且无多余逗号。

方案1：使用STRING_AGG（SQL Server 2017及以上版本，推荐） #

STRING_AGG是官方原生的分组拼接函数，语法简洁且性能更优，自动处理分隔符逻辑。

DECLARE @tbl TABLE
(
    ArtId INT, 
    ArtName VARCHAR(100), 
    ArtNo VARCHAR(50)
);

INSERT INTO @tbl (ArtId, ArtName, ArtNo)
SELECT 100, 'Red Dress', '0123456' UNION ALL
SELECT 110, 'Red Dress', '0123456' UNION ALL
SELECT 220, 'Red Dress', '0123456' UNION ALL
SELECT 308, 'Red Dress', '0123456' UNION ALL
SELECT 605, 'Red Dress', '0123456' UNION ALL
SELECT 999, 'Red Dress', '0123456' UNION ALL
SELECT 1110, 'Green Dress', '123455' UNION ALL
SELECT 1200, 'Purple Dress', '987654' UNION ALL
SELECT 1300, 'Purple Dress', '987654';

SELECT
    ArtNo,
    ArtName,
    STRING_AGG(ArtId, ',') WITHIN GROUP (ORDER BY ArtId) AS ArtIds
FROM @tbl
GROUP BY ArtNo, ArtName;

方案2：兼容低版本SQL Server（使用FOR XML PATH） #

通过FOR XML PATH拼接字符串，再用STUFF去除开头的多余逗号，最后用DISTINCT保证每组只返回一行。

DECLARE @tbl TABLE
(
    ArtId INT, 
    ArtName VARCHAR(100), 
    ArtNo VARCHAR(50)
);

INSERT INTO @tbl (ArtId, ArtName, ArtNo)
SELECT 100, 'Red Dress', '0123456' UNION ALL
SELECT 110, 'Red Dress', '0123456' UNION ALL
SELECT 220, 'Red Dress', '0123456' UNION ALL
SELECT 308, 'Red Dress', '0123456' UNION ALL
SELECT 605, 'Red Dress', '0123456' UNION ALL
SELECT 999, 'Red Dress', '0123456' UNION ALL
SELECT 1110, 'Green Dress', '123455' UNION ALL
SELECT 1200, 'Purple Dress', '987654' UNION ALL
SELECT 1300, 'Purple Dress', '987654';

SELECT DISTINCT
    t.ArtNo,
    t.ArtName,
    STUFF(
        (
            SELECT ',' + CAST(ArtId AS VARCHAR)
            FROM @tbl
            WHERE ArtNo = t.ArtNo AND ArtName = t.ArtName
            ORDER BY ArtId
            FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'),
        1, 1, ''
    ) AS ArtIds
FROM @tbl t;

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