SQL Server同表使用STUFF拼接字符串结果异常如何解决

需要实现的查询效果如下：

最初尝试使用的SQL语句如下：

SELECT
    STUFF((SELECT  ', ' + CONVERT(VARCHAR(50), RuleNumber)
           FROM #tempSelectPlusReferralsExtracts v2
           WHERE v2.RuleApprovedDate IN
                 (CASE WHEN (v2.RuleApprovedDate IS NULL ) 
                    THEN NULL
                    ELSE v2.RuleApprovedDate
                    END
                  )
           FOR XML PATH('')), 1, 2, '') [Rules], 
    *  
FROM
    #tempSelectPlusReferralsExtracts

运行后得到的结果不符合预期，实际效果如下：

问题出在STUFF子查询的关联条件错误，导致Rules字段拼接结果不符合要求。

• 子查询没有和外层主查询建立正确的分组关联，现有WHERE条件是子查询表v2的自指判断，没有关联外层表的对应字段，最终会把表中所有非NULL的RuleNumber全部拼接后返回给每一行
• 原有CASE判断是无效逻辑：CASE WHEN v2.RuleApprovedDate IS NULL THEN NULL ELSE v2.RuleApprovedDate END 等价于直接取v2.RuleApprovedDate，没有任何过滤作用
• 未处理NULL值的匹配逻辑：SQL中NULL和NULL做等值判断返回结果为UNKNOWN，无法匹配到RuleApprovedDate为NULL的同组记录

兼容所有SQL Server版本的写法（STUFF+FOR XML） #

给外层表加别名，修正子查询的关联逻辑，同时处理特殊字符转义问题：

SELECT
    STUFF((
        SELECT  ', ' + CONVERT(VARCHAR(50), v2.RuleNumber)
        FROM #tempSelectPlusReferralsExtracts v2
        WHERE 
            -- 关联同组数据，兼容NULL值匹配
            (v2.RuleApprovedDate = v1.RuleApprovedDate 
            OR (v2.RuleApprovedDate IS NULL AND v1.RuleApprovedDate IS NULL))
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS [Rules], 
    v1.*  
FROM #tempSelectPlusReferralsExtracts v1

SQL Server 2017及以上版本更简洁的写法 #

直接使用STRING_AGG窗口函数，不需要写关联子查询，性能更好、逻辑更简单：

SELECT
    STRING_AGG(CONVERT(VARCHAR(50), RuleNumber), ', ') 
        OVER(PARTITION BY RuleApprovedDate) AS [Rules],
    *
FROM #tempSelectPlusReferralsExtracts

• 明确区分内外层表别名，外层表使用v1别名，子查询使用v2别名
• 子查询关联条件改为匹配内外层相同RuleApprovedDate的记录，单独增加NULL值匹配逻辑，保证NULL分组的记录也能正确拼接
• 给FOR XML加TYPE参数后用value方法取值，避免&、<等特殊字符被转义为XML实体
• 高版本环境优先使用STRING_AGG窗口函数，写法更简洁，执行效率远高于关联子查询方案

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