sql - 将连接中的多个返回值转换为单个逗号分隔列表

Question

我有这个问题:

SELECT
    c.FirstName + ' ' + c.LastName AS Name,
    r.Name AS Roles
FROM
    Contacts c
    JOIN AspNetUsers u on c.ContactID = u.ContactID
    JOIN AspNetUserRoles ur on u.id = ur.UserId
    JOIN AspNetRoles r on ur.RoleId = r.Id

这会返回一个像这样的集合:

Name        Roles
-----------------------
Bob Jones   Admin
Bob Jones   Agent
Jane Smith  Broker
Jane Smith  Buyer

我想要的是:

Name        Roles
-----------------------
Bob Jones   Admin, Agent
Jane Smith  Broker, Buyer

我一直在研究 COALESCE 和 GroupBy，但我似乎无法找到正确的组合。

Answer 1

您可以使用 XML 字符串执行此操作，如您在 Google 上搜索“SQL Server 字符串聚合”时所看到的那样。

在您的情况下，由于 JOIN，查询可能并不明显。我认为这是最好的方法:

SELECT c.FirstName + ' ' + c.LastName AS Name,
       STUFF((SELECT ',' + r.Name
              FROM AspNetUsers u JOIN 
                   AspNetUserRoles ur 
                   ON u.id = ur.UserId JOIN
                   AspNetRoles r 
                   ON ur.RoleId = r.Id
              WHERE c.ContactID = u.ContactID
              FOR XML PATH (''), TYPE
             ).VALUE('.', 'nvarchar(max)'
                    ), 1, 1, '')
             as Roles
FROM Contacts c;

这里的要点是，如果可以避免的话，您不想在外部查询中进行聚合。相反，它使用相关的子查询。

注意:这不是完全等同的，因为它将返回没有角色的用户。