SQL command to merge rows with common ID?

Question

I have a table that looks like:

Event ID  Name
1         Bob
1         Steve
1         Tom
2         Bob
3         Steve
3         Tom

There are thousands of event IDs, and tens of unique names. I'd like an SQL query to return the following table:

Event ID  Names
1         Bob, Steve, Tom
2         Bob
3         Steve, Tom

I'm looking for an aggregate function like SUM() or AVG() except that it joins strings instead of does mathematics.

EDIT: I'm stuck using MS Access on this one.

EDIT 2: I realize that this would be trivial in a client language, but I'm trying to see if I can get an all-SQL solution.

What kind of DBMS are you using? A similar question was posted [here](https://stackoverflow.com/questions/15477743/listagg-in-sqlserver). MySQL has `GROUP_CONCAT()`, MSSQL has `STUFF()` and Oracle has `LISTAGG()`. — Simon, Jan 28 '14 at 19:33
In MSSQL, this is a pain to do, I usually end up making a CLR aggregation for concatenation. PostgreSQL, Oracle, and MySQL do this pretty handily. — Jeremy Holovacs, Jan 28 '14 at 19:58
Why do you want to do that **from SQL** (instead of just using a "normal" JOIN result)? SQL is for retrieving data, not formatting. Formatting can be done easily in the client language of your choice. — Branko Dimitrijevic, Jan 28 '14 at 20:20
@JeremyHolovacs Why do you say MySQL is "pain" for this - is there some problem with GROUP_CONCAT? — Branko Dimitrijevic, Jan 28 '14 at 20:25

Abe Miessler · Answer 1 · 2014-01-28T20:00:01.873

0

You don't mention which DBMS you are using but in MySQL it's pretty easy:

SELECT EventId, GROUP_CONCAT(Names)
FROM MyTable
GROUP BY EventId

In SQL Server it's a little trickier. The solution I typically see, requires that you use FOR XML PATH. You can find a good article on how to do this here.

edited Jan 28 '14 at 20:00

answered Jan 28 '14 at 19:53

Abe Miessler

82,532
99
305
486

score 0 · Answer 2 · answered Jan 28 '14 at 19:57

If you are using SQL server you could do something like this

SQL Fiddle Example

  WITH x AS
    (
      SELECT event_id FROM users
      GROUP BY event_id
    )
    SELECT x.event_id, 
           name = STUFF((SELECT ',' + name
                         FROM users WHERE event_id = x.event_id
                         FOR XML PATH('')), 1, 1, '')
    FROM x

score 0 · Answer 3 · answered Jan 28 '14 at 20:00

0

In PostgreSQL this would be:

select EventId, string_agg(name, ',') as names
from the_table
group by EventId;

If you want the names sorted in the list:

select EventId, string_agg(name, ',' order by name) as names
from the_table
group by EventId

answered Jan 28 '14 at 20:00

Thanks for the suggestion; good to know that this works in PostgreSQL. – stepthom Feb 04 '14 at 20:55

SQL command to merge rows with common ID?

3 Answers3