How to concatenate distinct column values from rows with duplicate IDs into one row?

Question

I have this query:

SELECT DISTINCT
            ces.CourseEventKey,
            up.Firstname + ' ' + up.Lastname
FROM        InstructorCourseEventSchedule ices
INNER JOIN  CourseEventSchedule ces ON ces.CourseEventScheduleKey = ices.MemberKey
INNER JOIN  UserProfile up ON up.UserKey = ices.UserKey
WHERE       ces.CourseEventKey IN
            (
                SELECT      CourseEventKey
                FROM        @CourseEvents
            )
ORDER BY CourseEventKey

It produces this result set:

CourseEventKey Name
-------------- --------------------
30             JACK K. BACKER
30             JEFFREY C PHILIPPEIT
30             ROBERT B. WHITE
33             JEFFREY C PHILIPPEIT
33             KENNETH J. SIMCICH
35             JACK K. BACKER
35             KENNETH J. SIMCICH
76             KENNETH J. SIMCICH
90             BARRY CRANFILL
90             KENNETH J. SIMCICH

The data is accurate, but I need the result set to look like this:

CourseEventKey Name
-------------- --------------------
30             JACK K. BACKER; JEFFREY C PHILIPPEIT; ROBERT B. WHITE
33             JEFFREY C PHILIPPEIT; KENNETH J. SIMCICH
35             JACK K. BACKER; KENNETH J. SIMCICH
76             KENNETH J. SIMCICH
90             BARRY CRANFILL; KENNETH J. SIMCICH

I've seen questions like mine with working solutions, but I cannot for the life of me adapt those solutions to work with my data.

How can I change my query to produce the 2nd result set using some form of concatenation?

Thanks in advance.

like this? http://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql?rq=1 — attila, Apr 17 '14 at 00:59

Szymon · Accepted Answer · 2014-04-17T01:23:24.657

5

You can use FOR XML PATH('') in an inner query to get the concatenated values and then use it to match with CourseEventKey from the outer query:

;WITH CTE
AS
(
    SELECT DISTINCT
            ces.CourseEventKey,
            up.Firstname + ' ' + up.Lastname AS Name
    FROM        InstructorCourseEventSchedule ices
    INNER JOIN  CourseEventSchedule ces ON ces.CourseEventScheduleKey = ices.MemberKey
    INNER JOIN  UserProfile up ON up.UserKey = ices.UserKey
    WHERE       ces.CourseEventKey IN
                (
                    SELECT      CourseEventKey
                    FROM        @CourseEvents
                )
)          

SELECT DISTINCT i1.CourseEventKey,         
    STUFF(
           (SELECT
                '; ' + Name
                FROM CTE i2
                WHERE i1.CourseEventKey = i2.CourseEventKey
                FOR XML PATH(''))
           ,1,2, ''
        )
FROM CTE i1
ORDER BY i1.CourseEventKey

edited Apr 17 '14 at 01:23

answered Apr 17 '14 at 01:04

Szymon

42,577
16
96
114

It looks like you may have just copy/pasted somebody else's answer and plugged in some of my table names. Could you take another look at my question and maybe rework your answer? – keeehlan Apr 17 '14 at 01:19
@kehrk I adopted my earlier answer from another question to this question. What exactly is wrong? – Szymon Apr 17 '14 at 01:22
The query returns nothing because there's key tables missing from the code and you're querying on the table variable I had previously declared. I modified the question to make a bit more sense. – keeehlan Apr 17 '14 at 01:22
Ok, I though you were going to use `@Instructors` to get the results. – Szymon Apr 17 '14 at 01:24
Nope, heheh. That's where the results were going. Simple misunderstanding. Thanks for your effort, by the way. – keeehlan Apr 17 '14 at 01:25
Ok, put your original query into a CTE to be used in my query. – Szymon Apr 17 '14 at 01:27
baller solution, helped me a bunch. Thanks! – StelioK Dec 06 '17 at 22:53

score 0 · Answer 2 · answered Apr 17 '14 at 03:51

You make a function that takes as a parameter of CourseEventScheduleKey and returns a concatenated string of the users. Then you can use it like:

    select CourseEventScheduleKey, 
           dbo.getUsersForCourse(CourseEventScheduleKey) as Users
    from CourseEventSchedule 
    order by CourseEventScheduleKey

This should return what you wants. The function looks like:

   create function getUsersForCourse(@CourseEventScheduleKey int)
        returns varchar(max)
        as
        begin
        declare @ret varchar(max)
        set @ret = ''

        select @ret = @ret + up.Firstname + ' ' + up.Lastname + '; '
        from CourseEventSchedule ces
        inner join InstructorCourseEventSchedule ices
        on ces.CourseEventScheduleKey = ices.MemberKey
        inner join UserProfile up
        on up.UserKey = ices.UserKey
        where ces.CourseEventScheduleKey = @@CourseEventScheduleKey
        order by up.Lastname, up.Firstname

        if(@ret = '')
            return @ret

        -- trim off the last semi colon and space
        return substring(@ret, 1, len(@ret) - 2) 

        end

score 0 · Answer 3 · answered Apr 17 '14 at 06:47

select distinct ces.CourseEventKey,STUFF((SELECT ', ' +up.Firstname + ' ' + up.Lastname) AS Name
          FROM UserProfile up  
          where UP.id = UserKey = ices.UserKey
          FOR XML PATH (''))
          , 1, 1, '')  AS Name) FROM        InstructorCourseEventSchedule ices
INNER JOIN  CourseEventSchedule ces ON ces.CourseEventScheduleKey = ices.MemberKey
    WHERE       ces.CourseEventKey IN
                (
                    SELECT      CourseEventKey
                    FROM        @CourseEvents
                )

Some of this syntax looks way off. – keeehlan Apr 17 '14 at 17:14 — keeehlan, Apr 17 '14 at 17:14

How to concatenate distinct column values from rows with duplicate IDs into one row?

3 Answers3