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.