SQL Concatenate rows from multiple tables into single text field separated by comma

Question

I have 3 tables

Projects, Consultant, Contact.

The Consultant table is a linking table containing only 3 fields, Projectid, Consultantid, and Contactid, which links a contact to a project as a consultant. There is no direct link from the contact to the project. The full name of the consultant is in the contact table. The Project Number is in the project table.

Project : ProjectNumber

Contact: Contactid, Consultant_Full_name

Consultant: Consultantid, Projectid, Contactid

I have built a table in SSRS showing project fields and want to pull in a column on the end showing all the consultants linked to each project

Example -

+---------------+--------------------------------------+
| ProjectNumber |             Consultants              |
+---------------+--------------------------------------+
|         12356 | Mary White, Fred Bloggs, Peter Jones |
|         12445 | Fred Bloggs, Paul White              |
+---------------+--------------------------------------+

The code I have written is:

SELECT t2.ProjectNumber
    ,consultant = STUFF((
            SELECT ',' + fullname
            FROM FilteredContact t1
            WHERE t1.contactid = t2.ConsultantID
            FOR XML PATH('')
            ), 1, 1, '')
FROM (
    SELECT proj.ccx_projectnumber AS ProjectNumber
        ,link.contactid AS ConsultantID
        ,con.fullname AS ConsultantName
    FROM FilteredContact con
    INNER JOIN Filteredccx_ccx_project_contact_consultant AS link ON con.contactid = link.contactid
    INNER JOIN Filteredccx_project proj ON link.ccx_projectid = proj.ccx_projectid
    ) t2
GROUP BY t2.ProjectNumber

I know that this is not grouped correctly as the Contactid needs to be in an aggregate or group by statement but I cannot work out how to do it correctly. I am thinking that maybe I have not linked the tables correctly. If I use

 group by ts.ProjectNumber, Contactid 

the results are a row per each consultant and not per each project which is what I want. Any help gratefully accepted.

I am using Sql server 2008

Answer 1

This is what you want

;WITH CTE
AS (
    SELECT proj.ccx_projectnumber AS ProjectNumber
        ,con.fullname AS ConsultantName
    FROM Filteredccx_ccx_project_contact_consultant AS link
    INNER JOIN Filteredccx_project proj ON link.ccx_projectid = proj.ccx_projectid
    INNER JOIN FilteredContact con ON link.contactid = con.contactid
    )
SELECT DISTINCT ProjectNumber
    ,STUFF((
            SELECT ',' + ConsultantName
            FROM CTE C1
            WHERE C.ProjectNumber = C1.ProjectNumber
            FOR XML PATH('')
            ), 1, 1, '')
FROM CTE C