Concatenation multiple rows and columns in SQL Server

Question

I have following tables.

Student table:

Id          Name
1           A
2           B

Marks table:

StudentId SubjectId Marks
1           1       67
1           2       89
2           1       78
2           2       86

I want to concat multiple rows for a given studentId and SubjectId, Marks columns into a single column as below.

Result:

Id          Name   SubjectMarks
1           A       1:67,2:89
2           B       1:78,2:86

I tried FOR XML PATH. It works for a single column, but for 2 columns I couldn't write a query.

Answer 1

You can do this without a CTE:

select s.id, s.name,
       stuff((select ',' + cast(subjectid as varchar(8000)) + ':' + cast(marks as varchar(8000))
             from marks m
             where m.studentid = s.id
             for xml path (''), type
             ).value('.', 'varchar(max)'), 1, 1, ''
            ) as SubjectMarks
from student s;