SSMS中如何将多用户组合并为单个单元格逗号分隔值

多表关联出现重复行，是因为AspNetUsers和UserGroups通过中间表UserGroupUsers是1对多关系，如果同时关联同样是1对多的角色表，还会产生笛卡尔积，导致同一条用户数据返回多行，直接在外层聚合很容易出现值重复、拼接错误的问题。

不要直接关联多对多表后在外层聚合，先将用户组、角色这类一对多关联的数据提前按用户ID分组聚合，每个用户仅返回一行聚合结果，再关联用户主表、岗位、部门这类一对一关联的维度表，从根源上避免重复行。

以下是适配SQL Server 2017及以上版本（支持STRING_AGG）的可直接运行代码：

SELECT 
    a.FirstName, 
    a.LastName, 
    a.Email, 
    JSON_VALUE(p.NameJson,'$[0].Text') AS Position, 
    JSON_VALUE(d.NameJson,'$[0].Text') AS Department, 
    ug.Groups,
    sr.Roles
FROM AspNetUsers a
LEFT JOIN Positions p 
    ON a.PositionId = p.Id
LEFT JOIN Departments d
    ON a.DepartmentId = d.Id
-- 预聚合当前用户关联的所有用户组
LEFT JOIN (
    SELECT 
        ugu.User_Id,
        STRING_AGG(JSON_VALUE(ug.NameJson, '$[0].Text'), ', ') AS Groups
    FROM UserGroupUsers ugu
    INNER JOIN UserGroups ug
        ON ugu.UserGroup_Id = ug.Id
    GROUP BY ugu.User_Id
) ug ON a.Id = ug.User_Id
-- 预聚合当前用户关联的所有角色，避免多角色+多用户组产生笛卡尔积
LEFT JOIN (
    SELECT 
        ur.UserId,
        STRING_AGG(JSON_VALUE(sr.TitleJson, '$[0].Text'), ', ') AS Roles
    FROM UserRoles ur
    INNER JOIN SecurityRoles sr
        ON ur.RoleId = sr.Id
    GROUP BY ur.UserId
) sr ON a.Id = sr.UserId
ORDER BY a.LastName DESC

运行后即可得到预期结果：单个用户仅返回一行，Groups列会将所有所属用户组用逗号拼接展示，其余字段保持正常。

低版本兼容（SQL Server 2016及更早） #

如果你使用的版本不支持STRING_AGG函数，将聚合子查询替换为FOR XML PATH + STUFF的写法即可，以用户组聚合为例，替换对应子查询部分：

LEFT JOIN (
    SELECT 
        ugu.User_Id,
        STUFF(
            (SELECT ', ' + JSON_VALUE(ug_sub.NameJson, '$[0].Text')
             FROM UserGroupUsers ugu_sub
             INNER JOIN UserGroups ug_sub 
                 ON ugu_sub.UserGroup_Id = ug_sub.Id
             WHERE ugu_sub.User_Id = ugu.User_Id
             FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),
            1, 2, ''
        ) AS Groups
    FROM UserGroupUsers ugu
    GROUP BY ugu.User_Id
) ug ON a.Id = ug.User_Id

角色部分的聚合逻辑和上面完全一致，替换对应字段和表名即可。

注意事项 #

• 禁止直接在原查询外层套GROUP BY+STRING_AGG：如果用户同时拥有多个角色、多个用户组，直接JOIN会产生「角色数*用户组数」的笛卡尔积行，最终拼接出来的群组、角色值会出现无意义的重复。
• 预聚合时已经完成了JSON字段的取值解析，外层查询不需要再重复调用JSON_VALUE处理组名、角色名字段。

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