How to concatenate all strings from a certain column for each group

Question

Suppose I have this table [Table1]

Name    Mark
------- ------
ABC     10
DEF     10
GHI     10
JKL     20
MNO     20
PQR     30

What should be my SQL statement to retrieve a record that looks like this: (group by [mark]). I have done the 1 and 2 columns but don't know how to accomplish the third column (concat the [name] with the same [mark])

mark count     names
---- -----     -----------
10       3     ABC,DEF,GHI
20       2     JKL,MNO
30       1     PQR

I'm using Microsoft SQL. Please help. Thanks

Answer 1

If MS SQL 2005 or higher.

declare @t table([name] varchar(max), mark int)

insert @t values ('ABC', 10), ('DEF', 10), ('GHI', 10),
    ('JKL', 20), ('MNO', 20), ('PQR', 30)


select t.mark, COUNT(*) [count]
    ,STUFF((
        select ',' + [name]
        from @t t1
        where t1.mark = t.mark
        for xml path(''), type
    ).value('.', 'varchar(max)'), 1, 1, '') [values]
from @t t
group by t.mark

---

Output:

mark        count       values
----------- ----------- --------------
10          3           ABC,DEF,GHI
20          2           JKL,MNO
30          1           PQR