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

Solution 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

Solution 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.

Solution 3

polishchuks solution is more elegant, but this is basically the same thing, we just deal with the trailing comma differently.

CREATE TABLE #Marks(Name nchar(3), Mark int)

INSERT INTO #Marks

SELECT 'ABC', 10 UNION ALL
SELECT 'DEF', 10 UNION ALL
SELECT 'GHI', 10 UNION ALL
SELECT 'JKL', 20 UNION ALL
SELECT 'MNO', 20 UNION ALL
SELECT 'PQR', 30 


SELECT 
    mark,  
    [count],
    CASE WHEN Len(Names) > 0 THEN LEFT(Names, LEN(Names) -1) ELSE '' END names  
    FROM
(
SELECT
    Mark,
    COUNT(Mark) AS [count], 
        (
        SELECT DISTINCT 
            Name + ', '
        FROM 
            #Marks M1
        WHERE M1.Mark = M2.Mark
        FOR XML PATH('')    
        ) Names 
FROM #Marks M2
GROUP BY Mark
) M

Comments

[Pankaj]

Does not make difference when not writing [values] ?

[Kirill Polishchuk]

@SQL, it's only alias, so no matter

[Ram]

I am not sure if you need the first distinct clause in the first subquery when you are doing group by mark, please correct me if I am wrong...

[Mikael Eriksson]

You should try this with a name like Barns & Noble. It's not pretty. To fix that you can do like this instead. stackoverflow.com/questions/6074321/…

[lanartri]

Link is not enough (and your blog post already has a dead link). You should copy the answer code here.