SSRS报表开发：多表关联实现员工角色名称合并输出求助

嘿，Rob，我看了你遇到的问题——现在你的SQL输出里角色名称重复，而且和合并后的RoleIDs不匹配，这是因为查询逻辑没处理好分组和关联关系。让我帮你梳理一下问题，然后给出两种可行的解决方案：

首先说说你当前SQL的问题所在：

• 你从Links表作为主表查询，而Links里每个员工对应多个角色行，所以每个角色都会生成一条记录，导致重复输出
• 角色名称的子查询只关联了当前行的RoleID，所以每次只能取出单个角色名称，而不是该员工的所有角色名称，这就造成了RoleIDs合并了所有角色，但RoleNames却逐行显示单个角色的混乱情况

接下来分两种情况给你解决方案，取决于你的SQL Server版本：

方案一：SQL Server 2017及以上版本（推荐） #

这个版本支持STRING_AGG函数，直接就能轻松合并字符串，代码简洁又不容易出错：

SELECT 
    s.StaffName,
    STRING_AGG(r.RoleName, ', ') AS RoleName
FROM Staff s
LEFT JOIN Links l ON s.StaffID = l.StaffID
LEFT JOIN Roles r ON l.RoleID = r.RoleID
GROUP BY s.StaffID, s.StaffName
ORDER BY s.StaffID;

逻辑说明： #

1. 以Staff表为主表，确保每个员工的基础信息唯一
2. 关联Links表拿到员工对应的所有角色ID，再关联Roles表拿到角色名称
3. 按员工ID和姓名分组，用STRING_AGG把同一个员工的所有角色名称用, 连接起来，自动处理分隔符，不会出现多余的逗号

方案二：SQL Server 2016及以下版本 #

如果你的数据库版本比较旧，就用FOR XML PATH的方式来合并字符串：

SELECT 
    s.StaffName,
    STUFF(
        (SELECT ', ' + r.RoleName 
         FROM Links l 
         INNER JOIN Roles r ON l.RoleID = r.RoleID
         WHERE l.StaffID = s.StaffID
         ORDER BY r.RoleName
         FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),
        1, 2, ''
    ) AS RoleName
FROM Staff s
ORDER BY s.StaffID;

逻辑说明： #

1. 同样以Staff表为主表，遍历每个员工
2. 子查询里针对当前员工，关联Links和Roles拿到所有角色名称，用FOR XML PATH('')把这些名称拼接成, 角色1, 角色2这样的字符串
3. 用STUFF函数把开头的, 去掉，得到干净的角色1, 角色2格式
4. TYPE和.value('.', 'NVARCHAR(MAX)')是为了避免特殊字符被转义（比如&变成&）

执行上面任意一种方案，都能得到你想要的输出格式：

StaffName | RoleName
----------+---------
Tom       | a
Dick      | b, c
Harry     | c, d

这样处理后的数据就可以直接用于SSRS报表啦，不用再处理重复行的问题。

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