How can I connect into one column all records that have the same ID?

Question

I have a table with 2 columns ID (not PK) and NAME. I want to take all the names that have the same ID and connect them into one column - how can I do this?

e.g.

ID                 Name
----               ----
1                   A
2                   B     
3                   C   
3                   D          
5                   E
3                   F

result :

1   A
2   B
3   CDF
5   E

In MySQL it's `GROUP_CONCAT()` - see these answer for Oracle versions: http://stackoverflow.com/questions/1120706/is-there-an-oracle-sql-query-that-aggregates-multiple-rows-into-one-row http://stackoverflow.com/questions/16771086/is-there-any-function-in-oracle-similar-like-group-concat-of-mysql — Daniel W., Oct 08 '13 at 07:34

score 1 · Accepted Answer · answered Oct 08 '13 at 07:40

1

Try this code

SELECT Id,GROUP_CONCAT(Name SEPARATOR ' ') FROM tablename GROUP BY Id;

In Oracle the GROUP_CONCAT() is name wm_concat().

answered Oct 08 '13 at 07:40

Mad Dog Tannen

7,129
5
31
55

I ran the code in oracle and got ora-00904 'wm_concat ivalid identifier'. Can you please add the exact syntax. thanx – user2663377 Oct 09 '13 at 11:40
Please check the database version you are using. wm_concat is available from 11g Release 2 – Mad Dog Tannen Oct 10 '13 at 07:28
I posted my comment yesterday and I didnt get a response. – user2663377 Oct 10 '13 at 14:02
Well you set it to Correct answer so i assumed it worked. What version of oracle are you running? – Mad Dog Tannen Oct 10 '13 at 14:02
Hi Kay, sorry the delay in my responce. In the end I found the answer in the link that you sent me. Thank you for you efforts in responce to my question. – user2663377 Oct 14 '13 at 06:50

score 0 · Answer 2 · answered Oct 08 '13 at 08:08

With MS Sql Server you may want to try this:

create table test
( id int, name varchar(10))

insert test (id,name) values (1,'A')
insert test (id,name) values (2,'B')
insert test (id,name) values (3,'C')
insert test (id,name) values (3,'D')
insert test (id,name) values (5,'E')
insert test (id,name) values (4,'F')


SELECT DISTINCT id AS Tabelle,
                Spalten = STUFF((SELECT DISTINCT ',' + name
                                   FROM test a
                                  WHERE a.id = b.id
                                 FOR XML PATH ('')), 1, 1, '')
from test b

How can I connect into one column all records that have the same ID?

2 Answers2