T
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)
