How to create a comma delimited list using SELECT statement from table columns

Question

Thanks in advance.

I have a table with values like this

class_id Instructor_Name

———————————

1 Joe 

2 Joe 

3 Joe 

1 Judy 

2 Judy 

2 Kevin 

3 Kevin

and I want the Result Set like this using SQL statement:

id name services (I want)

———————————–

1 Joe, Judy

2 Joe, Judy, KevinC

3 Joe, Kevin

How can I get this

Thanks, Brijesh

Check solution here: http://stackoverflow.com/questions/18513630/stringify-subquery-results-into-main-query — Renats Stozkovs, Feb 08 '17 at 19:08

score 6 · Accepted Answer · answered Feb 08 '17 at 19:10

6

Use STUFF and FOR XML PATH with correlated subquery:

select 
    class_id,
    stuff(
        (select ', ' + Instructor_Name from your_table b 
         where a.class_id = b.class_id for xml path('')),
        1, 2, ''
    ) names
from your_table a
group by
    class_id;

answered Feb 08 '17 at 19:10

Gurwinder Singh

38,557
6
51
76

Thanks a lot, GurV, worked like a charm. I was trying to find the previously posted question but was not able to find the right keyword for this. Thanks Again. – Brijesh Feb 08 '17 at 19:26

How to create a comma delimited list using SELECT statement from table columns

1 Answers1