STUFF与XML PATH函数使用问题：多方排查仍未解决

我懂你的困扰——你想把每个场所的多项服务合并成一个逗号分隔的Services字段，但写的查询结果和原查询没区别，根本没实现合并。问题出在两个关键地方：

错误根源 #

1. 子查询未关联主查询的场所：你的子查询没有限定只获取当前场所的服务，相当于把所有服务都拼了一遍，但因为主查询是多对多关联，每行都重复了这个拼接结果，再加上DISTINCT就和原查询看起来一样了。
2. 未按场所分组：你用了DISTINCT而不是GROUP BY，没办法让数据库知道要把同一个场所的多行记录合并成一行。

修正后的查询 #

SELECT 
    loc.MainAddress 
    ,loc.MainCity 
    ,loc.MainStateProvince_MisStateProvID 
    ,loc.MainZipPostal 
    -- 子查询关联当前场所的唯一标识，只拼接该场所的服务
    ,STUFF((
        SELECT ', ' + map.Name 
        FROM meditech_livefdb.dbo.MisNomenclatureMap_Main map 
        -- 关键：关联主查询的场所，确保只取当前场所的服务
        WHERE loc.SourceID = map.SourceID 
          AND loc.NomenclatureMap = map.MisNomenclatureMapID
        FOR XML PATH(''), TYPE
        -- 用value方法避免XML特殊字符被转义
    ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Services 
FROM meditech_livefdb.dbo.MisLoc_Main loc 
WHERE loc.MainAddress = '123 Main Street'
-- 按场所的唯一字段分组，保证每个场所只返回一行
GROUP BY 
    loc.MainAddress 
    ,loc.MainCity 
    ,loc.MainStateProvince_MisStateProvID 
    ,loc.MainZipPostal

关键改进点 #

• 移除主查询的LEFT JOIN：把关联逻辑放到子查询里，避免主查询因为多服务产生重复行，同时让子查询只聚焦当前场所的服务。
• 子查询添加关联条件：通过SourceID和NomenclatureMap匹配主查询的场所，确保拼接的是当前场所的服务，而不是所有服务。
• 用GROUP BY替代DISTINCT：明确按场所的核心字段分组，让数据库知道要将同一个场所的所有服务合并到一行。
• 处理XML特殊字符：添加TYPE和.value('.', 'NVARCHAR(MAX)')，可以避免像&、<这类特殊字符被转义成&、<，保证结果的准确性。

额外建议：如果MisLoc_Main表有唯一主键（比如类似MisLocID的字段），最好用主键来关联子查询，比用地址字段更可靠——毕竟有可能存在不同场所地址相同的情况，用主键能避免错误合并。

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