Concatenating by group

Question

I have the following table for students against subjects:

+----------+-------+-----+
|StudentID |Subject|Score|
+----------+-------+-----+
|1011010   |Phy    |54   |
+----------+-------+-----+
|1011020   |Phy    |78   |
+----------+-------+-----+
|1011010   |Maths  |76   |
+----------+-------+-----+
|1011030   |Maths  |65   |
+----------+-------+-----+

How do I show the result as a single concatenated string against each student? So,in the above data i should have the following returned:

+---------+---------------+
|StudentID|Result         |
+---------+---------------+
|1011010  |Phy-54,Maths-76|
+---------+---------------+
|1011020  |Phy-78,Maths-65|
+---------+---------------+

I am using Sql server 2008.

score 2 · Accepted Answer · answered Jan 21 '14 at 09:50

Try this

SELECT StudentID,
       ( SELECT Subject + '-' + Score + ','
           FROM students t2
          WHERE t2.StudentID = t1.StudentID
          ORDER BY Name
            FOR XML PATH('') ) AS Name
      FROM students t1
      GROUP BY StudentID ;

score -1 · Answer 2 · answered Jan 21 '14 at 09:53

-1

Stuff() shoud do it:

select t1.StudentID,
  STUFF(
       (SELECT ', ' + t2.Subject+ '-' + cast([Score] as varchar)
        FROM Students t2
        where t1.StudentID = t2.StudentID
        FOR XML PATH (''))
        , 1, 1, '')  AS Grades
from Students t1
group by studentID

answered Jan 21 '14 at 09:53

Kiril Rusev

745
3
9

1

You need to replace your query portion `, 1, 1, '') AS Grades` with `, 1, 0, '') AS Grades` – Vishal Patel Jan 21 '14 at 10:56
http://sqlfiddle.com/#!3/42409/1 Would you test it before making assumptions??? If I format it your way, I am going to leave the Leading comma IN. Thats why I have intentionally skipped it.Also, I would like to share the following example: Link. – Kiril Rusev Jan 21 '14 at 11:18
http://stackoverflow.com/questions/15843649/tsql-for-xml-path-failing-to-group – Kiril Rusev Jan 21 '14 at 11:24
I have tested it and then after give comments.. `Dhaval` answer is right. – Vishal Patel Jan 22 '14 at 09:21

Concatenating by group

2 Answers2