Concatenate column values I have a query that uses a couple joins to get a name from another table.

It works okay except that I would like to combine "Examiners Name" instead of having multiple duplicates:

ID    |  Name       | Examiners Name
1290  | Jun         | Aron
1290  | Jun         | Mark
1292  | Takahiro    | Didier
1292  | Takahiro    | Eiro
1292  | Takahiro    | Kurt 


What I am looking for:

ID    |  Name       | Examiners Name
1290  | Jun         | Aron, Mark
1292  | Takahiro    | Didier, Eiro, Kurt


Here is my query:

SELECT DISTINCT
usr.CandidateID,
usr.FirstName,
Examiner.FirstName AS [Examiners Name]
FROM
dbo.tbl_User AS usr
LEFT JOIN dbo.Tbl_ExaminerCandidates AS ExamCa ON usr.UserID = ExamCa.CandidateID
LEFT JOIN dbo.tbl_User AS Examiner ON ExamCa.ExaminerID = Examiner.UserID
WHERE
usr.CandidateID IS NOT NULL AND
usr.IsActive = 1 AND
usr.UserStatus = 1


I have been looking around but I cannot find a way to do this. I am using SQL Server 2008.
You can use FOR XML PATH() clause to generate the desired output.

I'm starting from your result set and generate the output.

DECLARE @Table1 TABLE
    ([ID] int, [Name] varchar(8), [Examiners Name] varchar(6))
;

INSERT INTO @Table1
    ([ID], [Name], [Examiners Name])
VALUES
    (1290, 'Jun', 'Aron'),
    (1290, 'Jun', 'Mark'),
    (1292, 'Takahiro', 'Didier'),
    (1292, 'Takahiro', 'Eiro'),
    (1292, 'Takahiro', 'Kurt')
;

SELECT
    C.[ID],
    C.[Name],
    STUFF(CA.[Ex_Name],1,1,'') AS [Examiners Name]
FROM
    (SELECT DISTINCT 
        [ID],
        [Name]
     FROM 
        @Table1) AS C
     CROSS APPLY
     (
        SELECT
            ','+E.[Examiners Name]
        FROM 
            @Table1 AS E
        WHERE 
            C.[ID] = E.[ID]
        ORDER BY E.[Examiners Name]
        FOR XML PATH('')
    )CA(Ex_Name)


The output for this :

ID          Name     Examiners Name
1290        Jun      Aron,Mark
1292        Takahiro Didier,Eiro,Kurt


If you provide the DDL for your table , we can post a  query base on these tables.

And base on your raw tables:(something that is not tested)

SELECT 
usr.CandidateID,
usr.FirstName,
STUFF(Examiner.FirstName,1,1,'') AS [Examiners Name]
FROM
(
    SELECT DISTINCT
        usr.CandidateID,
        usr.FirstName,
        ExamCa.ExaminerID
    FROM
        dbo.tbl_User AS usr
        LEFT JOIN dbo.Tbl_ExaminerCandidates AS ExamCa 
            ON usr.UserID = ExamCa.CandidateID
    WHERE
        usr.CandidateID IS NOT NULL AND
        usr.IsActive = 1 AND
        usr.UserStatus = 1
)AS USR
OUTER APPLY
(
    SELECT 
        ','+ E.FirstName
    FROM
        dbo.tbl_User AS E
    WHERE
        E.UserID = USR.ExaminerID
    FOR XML PATH('')
)Examiner(FirstName)