sqlsql-servert-sqlxquerysql-server-group-concat

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


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


Solution

  • 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