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

See also http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 — Ed Guiness, Jul 05 '11 at 08:14

Kirill Polishchuk · Accepted Answer · 2011-07-05T12:34:59.010

35

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

edited Jul 05 '11 at 12:34

answered Jul 05 '11 at 08:04

Kirill Polishchuk

54,804
11
122
125

Does not make difference when not writing [values] ? – Pankaj Jul 05 '11 at 09:05
@SQL, it's only alias, so no matter – Kirill Polishchuk Jul 05 '11 at 09:13
2

You should try this with a name like `Barns & Noble`. It's not pretty. To fix that you can do like this instead. http://stackoverflow.com/questions/6074321/sql-for-xml-path-list-and-count/6077206#6077206 – Mikael Eriksson Jul 05 '11 at 11:44

score 4 · Answer 2 · answered Jul 06 '11 at 17:43

4

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.

answered Jul 06 '11 at 17:43

Jerry Nixon

31,313
14
117
233

Link is not enough (and your blog post already has a dead link). You should copy the answer code here. – iDevlop Feb 25 '19 at 13:12

woggles · Answer 3 · 2011-07-05T10:18:21.147

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

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... — Ram, Jul 05 '11 at 09:39

score 0 · Answer 4 · answered Mar 16 '14 at 01:16

Loosely based on Itzik Ben-Gan, Inside Microsoft SQL Server 2005: T-SQL Programming, p. 215:

IF OBJECT_ID('dbo.Table1') IS NOT NULL 
    DROP TABLE dbo.Table1 ;
GO
CREATE TABLE dbo.Table1 ( Name VARCHAR(10), Mark INT ) ;

INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( 'ABC',     10 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( 'DEF',     10 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( 'GHI',     10 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( 'JKL',     20 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( 'MNO',     20 ) ;
INSERT INTO dbo.Table1 ( Name, Mark ) VALUES ( 'PQR',     30 ) ;


WITH DelimitedNames AS
(
    SELECT Mark, T2.Count,
        (   SELECT Name + ',' AS [text()]
            FROM dbo.Table1 AS T1
            WHERE T1.Mark = T2.Mark
            ORDER BY T1.Mark
            FOR XML PATH('')) AS Names
    FROM ( SELECT Mark, COUNT(*) AS Count FROM dbo.Table1 GROUP BY Mark ) AS T2 
)
SELECT Mark, Count, LEFT(Names, LEN(NAMES) - 1) AS Names
FROM DelimitedNames ;

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

4 Answers4

Linked