SQL Server concatenate a column's values on subquery

Question

I have three table as:

Student: id,name
Lesson: id,name
Student_Lesson: student_id, lesson_id

I want to create a query such as:

Select s.name as student_name,
       (
       Select l.name 
           from Lesson as l 
           inner join Student_Lesson as sl on sl.lesson_id=l.id
               where sl.student_id=s.id
       ) as lessons
       from Student as s where <Complex query>

This gives the error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

As expected, because there is more than one lesson attached with a student, can I concatenate all results and have a result such as:

_____________________________________________
| student_name    |    lessons              |
| ----------------|-------------------------|
| Luke Skywalker  |    Physics,Chemistry    |
| Han Solo        |    Mathematics,Physics  |
|_________________|_________________________|

If I reduce the lesson count for all students to one this query works fine.

There may be a syntax error as I did refactor my query and I might have made some mistakes.

possible duplicate of [How do I Create a Comma-Separated List using a SQL Query?](http://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query) — Jeroen Mostert, Oct 22 '14 at 14:00
Also possible duplicates: http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server and http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — jpw, Oct 22 '14 at 14:02

score 3 · Accepted Answer · answered Oct 22 '14 at 14:26

Using XML PATH:

create table #student (
    id int identity(1,1),
    name varchar(100)
)
create table #lesson (
    id int identity(1,1),
    name varchar(100)
)
create table #student_lesson (
    student_id int,
    lesson_id int
)

insert into #student values
('Luke Skywalker'), ('Han Solo')

insert into #lesson values
('Chemistry'), ('Mathematics'), ('Physics')

insert into #student_lesson values
(1, 3), (1, 1), (2, 2), (2, 3)

select
    student_name = s.name,
    lessons = stuff((
        select ', ' + name 
        from #lesson t
        inner join #student_lesson t2
            on t.id = t2.lesson_id
        where t2.student_id = s.id
        group by t.name
        for xml path(''), type).value('.', 'varchar(max)')
    , 1, 2, '')
from #student s

drop table #student
drop table #lesson
drop table #student_lesson

SQL Server concatenate a column's values on subquery

1 Answers1