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 

Answer 2

Here's a performance-related answer!

http://jerrytech.blogspot.com/2010/04/tsql-concatenate-strings-1-2-3-and.html

Using XML functions in a large query is a performance killer.

Using a CTE is a performance superstar.

Check out the link, it will explain how.

I admit the work to accomplish it is more.

But the result is milliseconds over millions of rows.