title: SQL Server concatenate a column's values on subquery
body: <p>I have three table as:</p>
  
  <pre><code>Student: id,name
  Lesson: id,name
  Student_Lesson: student_id, lesson_id
  </code></pre>
  
  <p>I want to create a query such as:</p>
  
  <pre><code>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 &lt;Complex query&gt;
  </code></pre>
  
  <p>This gives the error:</p>
  
  <blockquote>
    <p>Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , >, >= or when the subquery is used as an expression.</p>
  </blockquote>
  
  <p>As expected, because there is more than one lesson attached with a student, can I concatenate all results and have a result such as:</p>
  
  <pre><code>_____________________________________________
  | student_name    |    lessons              |
  | ----------------|-------------------------|
  | Luke Skywalker  |    Physics,Chemistry    |
  | Han Solo        |    Mathematics,Physics  |
  |_________________|_________________________|
  </code></pre>
  
  <p>If I reduce the lesson count for all students to one this query works fine.</p>
  
  <p>There may be a syntax error as I did refactor my query and I might have made some mistakes.</p>