使用STUFF/SELECT...FOR XML实现行转列时的困惑与求助

问题背景 #

需从第三方租赁数据库生成报表，核心表UNITXREF（别名X）关联租约、物业、租户及多个单元。例如FedEx的A.2021租约租用物业"br"的101、102单元，在X表中对应两行。

当前查询结果 #

propcode    tenname unit    area    months  startdt    enddt
br          FE      101     30876   63      2021-08-01 2026-10-31 
br          FE      102     30876   63      2021-08-01 2026-10-31

期望结果 #

propcode    tenname units   area    months  startdt    enddt
br          FE      101,102 30876   63      2021-08-01 2026-10-31

限制条件 #

• 仅允许使用SELECT语句，无法创建表、临时表或游标
• 运行环境为SQL Server 2017之前版本

原查询问题分析 #

你尝试用FOR XML PATH+STUFF实现合并，但仍返回多行单单元记录，核心原因有两点：

1. 分组维度错误：原查询GROUP BY中包含了inside.hUnit，强制按单个单元分组，导致每个单元单独成一行，无法合并。
2. 子查询关联逻辑错误：子查询用inside.hUnit=ug.hmy关联，仅查询当前行对应的单个单元，而非同一租约下的所有单元。

解决方法 #

调整查询逻辑，外层按租约+租户+物业的唯一组合分组，子查询关联同一租约下的所有单元：

SELECT 
    p.scode AS propcode,
    t.slastname AS tenname,
    main.dcontractarea AS area,
    main.iterm AS months,
    main.dtstart AS startdt,
    main.dtend AS enddt,
    STUFF((
        SELECT ', ' + ug.scode
        FROM UNITXREF x_sub
        JOIN UNIT ug ON x_sub.hUnit = ug.hmy
        WHERE x_sub.hTenant = main.hTenant 
          AND x_sub.hAmendment = main.hAmendment
          AND x_sub.dtLeasefrom > '2021-01-01'
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 2, '') AS units
FROM (
    SELECT DISTINCT
        x.hTenant,
        x.hAmendment,
        u.HPROPERTY,
        a.dcontractarea,
        a.iterm,
        a.dtstart,
        a.dtend
    FROM UNITXREF x
    JOIN UNIT u ON u.hmy = x.hUnit
    JOIN COMMAMENDMENTS a ON x.hAmendment = a.hmy
    WHERE x.dtLeasefrom > '2021-01-01'
) AS main
JOIN PROPERTY p ON p.HMY = main.HPROPERTY
JOIN TENANT t ON t.HMYPERSON = main.hTenant
GROUP BY 
    p.scode,
    t.slastname,
    main.dcontractarea,
    main.iterm,
    main.dtstart,
    main.dtend

关键调整说明 #

• 外层用DISTINCT子查询先获取唯一的租约-租户-物业组合，避免重复分组
• 子查询通过hTenant和hAmendment关联到同一租约下的所有单元，而非单个单元
• 使用TYPE和.value()方法避免XML转义字符问题（如单元编码含特殊字符时不会被转义）
• 移除GROUP BY中的hUnit，确保按租约维度合并单元

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