0

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.

2

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)
| improve this answer | |
1

You can concatenate string using FOR XML for each [Name].

Query:

SELECT ID, Name, [Examiner Names] = LEFT(Names, LEN(Names) - 1)
FROM (
    SELECT DISTINCT ID, Name
        , Names = (
            SELECT [ExaminerName] + ',' AS 'data()' 
            FROM names 
            WHERE ID = x.ID
            ORDER BY [ExaminerName]
            FOR XML PATH('')
        )
    FROM names x
) n

SQL Fiddle with sample table and query.

Output:

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

Using your original query, it would look like this:

WITH names AS (
    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
)
SELECT CandidateID, FirstName, [Examiner Names] = LEFT(Names, LEN(Names) - 1)
FROM (
    SELECT DISTINCT CandidateID, FirstName
        , Names = (
            SELECT [Examiners Name] + ',' AS 'data()' 
            FROM names 
            WHERE CandidateID = x.CandidateID
            ORDER BY [Examiners Name]
            FOR XML PATH('')
        )
    FROM names x
) n

In order to make it easier to read, your query has been enclosed within a CTE.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.