SQL Server 2012中使用FOR XML PATH实现成本中心逗号拼接结果异常

你原代码无法得到预期结果，核心是3处逻辑问题：

• CTE内部的表连接缺失关联条件：你用逗号分隔写两个表FROM [FAV_VS_STAFF_M] a, [FAV_VS_COST_CENTER_M] b，没有设置两表的关联匹配规则，会直接生成全量笛卡尔积——也就是把所有员工和所有成本中心做无差别交叉组合，完全偏离你需要的「员工绑定对应成本中心」的基础数据集，后续聚合自然完全失真。
• 列名不匹配：最终查询里拼接出来的字段别名为FieldBs，没有设置为预期的CostCenter，输出结构和需求不一致。
• 细节偏差：拼接时用的分隔符是纯逗号，没有带预期的空格；同时FOR XML PATH没有做特殊字符转义防护，遇到含<、&的成本中心描述时会输出XML实体编码，出现乱码。

WITH CTE_TableName AS 
(
    SELECT   
        a.Staff_No, 
        a.Name,  
        CONVERT(varchar(19), a.COST_CENTER_CODE) + ' - ' + b.COST_CENTER_DESC AS CostCenter,   
        CASE a.Active 
           WHEN 1 THEN 'Active' 
           WHEN 2 THEN 'Inactive' 
        END AS status   
    FROM [FAV_VS_STAFF_M] a
    -- 按实际表结构调整关联键，此处示例以成本中心编码作为两表匹配规则
    INNER JOIN [FAV_VS_COST_CENTER_M] b 
        ON a.COST_CENTER_CODE = b.COST_CENTER_CODE
)
SELECT 
    t0.Staff_No, 
    t0.Name, 
    -- 拼接列别名与预期输出对齐
    STUFF(
        (
            -- 分隔符调整为逗号加空格，匹配预期的输出格式
            SELECT ', ' + t2.CostCenter
            FROM CTE_TableName t2
            -- 扩展匹配条件，避免同员工号下不同状态、不同名称的数据串值
            WHERE t2.Staff_No = t0.Staff_No
              AND t2.Name = t0.Name
              AND t2.status = t0.status
            ORDER BY t2.CostCenter
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(8000)'), 1, 2, '') AS CostCenter,
    t0.status
FROM CTE_TableName t0
GROUP BY 
    t0.Staff_No , 
    t0.Name, 
    t0.status
ORDER BY 
    t0.Staff_No;

• 输出的拼接字段完全匹配示例格式：同一员工下的多个成本中心会以xxx , yyy的逗号分隔格式合并，单成本中心的员工不会带多余分隔符
• 增加TYPE和.value()处理逻辑后，可彻底规避FOR XML的特殊字符转义问题，不会出现成本中心描述含特殊符号时的乱码
• 关联匹配条件从仅匹配员工号扩展到匹配姓名、状态，避免历史数据变动（比如同员工号曾改过姓名、状态）导致的错误拼接

注意：两表的关联键需要和你实际库表结构完全匹配，如果你表里员工和成本中心是通过中间关联表实现多对多关系（而非直接在员工表存成本中心编码），需要把关联表的连接逻辑补全到CTE里。

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