sql - TSQL 逗号分隔

Question

我正在编写一个导出函数，需要将联系人导出到 Excel，但我遇到了技术障碍 - 或者也许我的 SQL 技能差距更接近事实。 ;)

场景如下:我的数据库中有很多联系人。每个联系人可以有许多不同的角色，例如联系人可以是 C# 开发人员和 DBA，或者 DBA 和 IT 经理。它们分为三个表，如下所示:

-------------------   -------------------   -------------------
*     Contact     *   *   ContactRole   *   *      Role       *
-------------------   -------------------   -------------------
* ID              *   * ContactID       *   * ID              *
* Name            *   * RoleID          *   * Name            *
* Address         *   -------------------   -------------------
-------------------   

不太难理解。有一组联系人和一组角色。这些由相应 ID 上的 ContactRole 表连接起来。

导出联系人时，我需要在导出中有一列，所有角色均以逗号分隔，例如 C# Developer, DBA 或 DBA, IT-manager 。导出将从 ASP.NET/C# 代码隐藏中完成，因此我想如果需要的话，我可以在代码中完成此操作，但我感觉可以在 SQL 中完成。

数据来自SQL Server 2005。

Answer 1

仅仅因为您使用 SQL Server 2005(如果您幸运并且正确设置了所有 XML 设置)，这里是您的简单 SQL 查询(纯 SQL，无函数):

SELECT  c.ID, c.Name, c.Address, 
    (   SELECT      r.Name + ','
        FROM        "ContactRole" cr
        INNER JOIN  "Role" r
                ON  cr.RoleID = r.ID
        WHERE       cr.ContactID = c.ID
        ORDER BY    r.ID --r.Name
        FOR XML PATH('')
    ) AS "Roles"
FROM    "Contact" c

要测试它是否适合您，只需执行下面的整个代码段:

WITH "Contact" (ID, Name, Address) AS (
                SELECT 1, 'p1-no role', NULL
    UNION ALL   SELECT 2, 'p2-one role', NULL
    UNION ALL   SELECT 3, 'p3-two roles', NULL
)
, "Role" (ID, Name)AS (
                SELECT 1, 'teacher'
    UNION ALL   SELECT 2, 'student'
)
, "ContactRole" (ContactID, RoleID) AS (
                SELECT 2, 1
    UNION ALL   SELECT 3, 1
    UNION ALL   SELECT 3, 2
)

SELECT  c.ID, c.Name, c.Address, 
    (   SELECT      r.Name + ','
        FROM        "ContactRole" cr
        INNER JOIN  "Role" r
                ON  cr.RoleID = r.ID
        WHERE       cr.ContactID = c.ID
        ORDER BY    r.ID --r.Name
        FOR XML PATH('')
    ) AS "Roles"
FROM    "Contact" c

您应该得到以下结果:

ID          Name         Address     Roles
----------- ------------ ----------- ------------------
1           p1-no role   NULL        NULL
2           p2-one role  NULL        teacher,
3           p3-two roles NULL        teacher,student,