0

How can I do a concatenation of comma separated course IDs that for each student?

select student.ID,
  concat(student_course.COURSE_ID,',') 
  from student.ID join student_course on student.ID=student_course.STUDENT_ID
group by student.ID

Student Id  |  Course Id
1           |  101, 230, 432
2           |  293, 101
softwarematter
  • 28,015
  • 64
  • 169
  • 263

1 Answers1

1

Use the below script using 'STUFF"

SELECT  ID [Student ID]
       ,STUFF((SELECT ', ' + CAST(COURSE_ID AS VARCHAR(10)) [text()]
         FROM student_course 
         WHERE STUDENT_ID =s.ID
         FOR XML PATH(''), TYPE)
        .value('.','NVARCHAR(MAX)'),1,2,' ') [Course Id]
FROM student s
GROUP BY ID

Here comes another method using 'Cross Apply'.

SELECT  Id as [Student ID],[Course ID]
FROM    (
        SELECT  [Id]
        FROM    student
        ) a
CROSS APPLY
        (
        SELECT  CASE ROW_NUMBER() OVER(ORDER BY COURSE_ID) WHEN 1 THEN '' ELSE ', ' END +CAST(COURSE_ID as varchar(50))
        FROM    student_course b
        WHERE   a.id = b.STUDENT_ID
        ORDER BY COURSE_ID
        FOR XML PATH ('')
        ) b([Course ID])
Unnikrishnan R
  • 4,965
  • 1
  • 12
  • 21
  • If you do not expect forbidden characters this can be put a bit simpler without the `.values`-call (untested): `SELECT ID [Student Id] ,STUFF((SELECT ', ' + CAST(c.COURSE_ID AS VARCHAR(10)) FROM student_course AS c WHERE c.STUDENT_ID =s.ID FOR XML PATH('')),1,2,'') [Course Id] FROM student s GROUP BY ID` – Shnugo Oct 05 '16 at 09:16
  • @Shungo - agree with you.. – Unnikrishnan R Oct 05 '16 at 09:20