SQL Server 2014查询重复SUBSTITUTECODE对应逗号分隔CODE集合方法

因为SQL Server 2014版本未提供高版本才支持的STRING_AGG字符串聚合函数，所以需要使用FOR XML PATH结合STUFF的方案实现分组拼接逗号分隔字符串，完整查询如下：

SELECT
    CODES = STUFF((
        SELECT ',' + prod_inner.CODE
        FROM @SUBSTITUTE sub_inner
        INNER JOIN @MATERIAL prod_inner ON prod_inner.ID = sub_inner.ITEID
        WHERE sub_inner.SUBSTITUTECODE = sub_main.SUBSTITUTECODE
        FOR XML PATH(''), TYPE
    ).value('.', 'varchar(1000)'), 1, 1, ''),
    sub_main.SUBSTITUTECODE
FROM @SUBSTITUTE sub_main
INNER JOIN @MATERIAL prod_main ON prod_main.ID = sub_main.ITEID
GROUP BY sub_main.SUBSTITUTECODE
HAVING COUNT(sub_main.SUBSTITUTECODE) >= 2

逻辑说明 #

• 内层子查询通过FOR XML PATH('')将同一SUBSTITUTECODE下的所有CODE拼接为前缀带逗号的字符串
• STUFF函数移除拼接字符串开头多余的第一个逗号，得到标准逗号分隔的编码集合
• 外层查询通过GROUP BY和HAVING条件筛选出关联物料数≥2的替换编码，最终得到符合要求的结果集

运行后输出结果和预期完全一致：

内容的提问来源于stack exchange，提问作者Faye D.