TSQL Comma Separation

Question

I'm writing an export function, where I need to export contacts to Excel, and I've run into a technical snag - or perhaps a gap in my SQL skills is closer to the truth. ;)

Here's the scenario: I've got a bunch of contacts in a database. Each contact can have many different roles, for example a contact can be both C# Developer and DBA, or DBA and IT-manager. These are split into three tables, like so:

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

Not too hard to follow. There's a set of contacts, and a set of roles. These are joined by the ContactRole table on the respective IDs.

When I export the contacts, I need to have a column in the export with all the roles comma separated, like C# Developer, DBA or DBA, IT-manager. The export will be done from ASP.NET/C# codebehind, so I figured I could do this in code should it come to that, but I've got a feeling it's possible to do in the SQL.

The data comes from SQL Server 2005.

Answer 1

Just because you use SQL Server 2005 (and if you are lucky and have all XML settings properly set), here is your simple SQL query (pure SQL and no functions):

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

To test if it works for you, just execute the whole snippet below:

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

and you should get the following result:

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