How to concatenate many rows with same id in sql?

Question

My table contains the details like with two fields:

ID      DisplayName
1        Editor
1        Reviewer
7        EIC
7        Editor
7        Reviewer
7        Editor
19       EIC
19       Editor
19       Reviewer

I want get the unique details with DisplayName like

1 Editor,Reviewer 7 EIC,Editor,Reviewer

Don't get duplicate value with ID 7

How to combine DisplayName Details? How to write the Query?

You want to have 2 columns as for now? `ID` and `DisplayName` just for the same id you want comma separate names? Or you want to get all results in one row as you wrote? — Stanislovas Kalašnikovas, Feb 03 '16 at 09:26
@StanislovasKalašnikovas: I would like get all result but I don't need get duplicate value. ID = 7 I have 2 Editor but I would like get one. — Luc Le, Feb 03 '16 at 09:30
Possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) — sagi, Feb 03 '16 at 09:31

Stanislovas Kalašnikovas · Accepted Answer · 2019-02-25T13:56:53.817

37

In SQL-Server you can do it in the following:

QUERY

SELECT id, displayname = 
    STUFF((SELECT DISTINCT ', ' + displayname
           FROM #t b 
           WHERE b.id = a.id 
          FOR XML PATH('')), 1, 2, '')
FROM #t a
GROUP BY id

TEST DATA

create table #t 
(
id int,
displayname nvarchar(max)
)

insert into #t values    
 (1 ,'Editor')
,(1 ,'Reviewer')
,(7 ,'EIC')
,(7 ,'Editor')
,(7 ,'Reviewer')
,(7 ,'Editor')
,(19,'EIC')
,(19,'Editor')
,(19,'Reviewer')

OUTPUT

id  displayname
1   Editor, Reviewer
7   Editor, EIC, Reviewer
19  Editor, EIC, Reviewer

edited Feb 25 '19 at 13:56

answered Feb 03 '16 at 09:30

Stanislovas Kalašnikovas

9,203
4
32
59

2

what does `, 1, 2, ''` means after the stuff? – guradio Jun 14 '19 at 05:38
2

@guradio this is to remove the leading comma and space that would otherwise appear. They are arguments for [STUFF](https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver15): start = 1, length = 2, replacement value = '', ie. the **first two** characters are replaced with **''** – bernhof Oct 25 '19 at 13:31

score 11 · Answer 2 · answered Apr 16 '21 at 12:06

SQL Server 2017+ and SQL Azure: STRING_AGG

Starting with the next version of SQL Server, we can finally concatenate across rows without having to resort to any variable or XML witchery.

STRING_AGG (Transact-SQL)

SELECT ID, STRING_AGG(DisplayName, ', ') AS DisplayNames
FROM TableName
GROUP BY ID

score 10 · Answer 3 · answered Feb 03 '16 at 09:29

DECLARE @t TABLE
(
    ID INT,
    DisplayName VARCHAR(50)
)
INSERT INTO @t (ID, DisplayName)
VALUES
    (1 , 'Editor'),
    (1 , 'Reviewer'),
    (7 , 'EIC'),
    (7 , 'Editor'),
    (7 , 'Reviewer'),
    (7 , 'Editor'),
    (19, 'EIC'),
    (19, 'Editor'),
    (19, 'Reviewer')

SELECT *, STUFF((
            SELECT DISTINCT ', ' + DisplayName
            FROM @t
            WHERE ID = t.ID
            FOR XML PATH('')), 1, 2, '')
FROM (
    SELECT DISTINCT ID
    FROM @t
) t

Output -

----------- ------------------------
1           Editor, Reviewer
7           Editor, EIC, Reviewer
19          Editor, EIC, Reviewer

My post about string aggregation:

http://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server

Finally got it to work with this sample. Just replace @t with a subselect that look like the temp table and it'll just work! — Simon Dufour, Jul 09 '19 at 14:31

Monami J · Answer 4 · 2017-07-31T08:55:14.810

10

For MySQL:
SELECT id, GROUP_CONCAT(displayname) FROM tableName GROUP BY id

Refer: http://www.sqlines.com/mysql/functions/group_concat

edited Jul 31 '17 at 08:55

answered Jul 31 '17 at 08:50

Monami J

329
3
5

2

SQL-Server does not support this notation. – vahdet Jun 18 '18 at 09:29
Thanks, works very well. I wish we could change the separator! – rdonuk Nov 05 '19 at 09:05

score 3 · Answer 5 · answered Jan 03 '17 at 07:01

3

and in case of oracle database

select id, 
       listagg(displayname, ',') within group (order by displayname) as names
from test
group by id

answered Jan 03 '17 at 07:01

venkat

31
1

score 1 · Answer 6 · edited Sep 01 '20 at 05:39

1

to change the separator use

SELECT id, GROUP_CONCAT(displayname SEPARATOR ';') FROM tableName GROUP BY id

this will change separator from comma to semicolon :)

edited Sep 01 '20 at 05:39

Suraj Rao

29,388
11
94
103

answered Sep 01 '20 at 05:37

Abhishek Tomar

11
1

score 0 · Answer 7 · answered Feb 03 '16 at 09:38

0

Thank you all,

SELECT Distinct
    t1.ID,
    MAX(STUFF(t2.x_id,1,1,'')) AS DisplayName
FROM Table t1
CROSS apply(
    SELECT Distinct ', ' + SUBSTRING(t2.DisplayName,1,2)
    FROM Table t2
    WHERE t2.ID = t1.ID AND t2.DisplayName > ''
    FOR xml PATH('')
) AS t2 (x_id)
GROUP BY
    t1.ID
order by 1
GO

answered Feb 03 '16 at 09:38

Luc Le

379
1
5
13

1

Just so you know. When there are alot of rows in the table, this method is will be much slower than Devart's and Stan's answers – t-clausen.dk Feb 03 '16 at 09:59
what does `, 1, 2, ''` means after the stuff? – guradio Jun 14 '19 at 05:39

score 0 · Answer 8 · answered Mar 20 '23 at 13:03

0

In Oracle SQL, you can use LISTAGG.

SELECT ID, LISTAGG(DisplayName, ', ') as all_agg
FROM TABLE_NAME GROUP BY ID;

answered Mar 20 '23 at 13:03

Sreekant Shenoy

1,420
14
23

How to concatenate many rows with same id in sql?

8 Answers8

Linked

Related