How to group multiple rows from SQL into one

by Csaba Kissi
Published: July 14, 2021
mysql sql

Sometimes there may be a situation when you need to group data differently. Let's say we have an SQL table with two columns and the following schema (this is for MySQL):

create table the_table (
    proposal_id integer,
    item_id  integer);

insert into the_table values
(1,              83054),
(1,              81048),
(2,              71384),
(2,              24282),
(2,              19847),
(2,              18482),
(3,              84720),
(4,              18081),
(4,              73018);

We want to have a unique proposal_id in the first column in the result and the second column should contain all the items_ids separated by commas like:

| proposal_id | item_ids               |
|-------------|----------------------- |
| 1           | 83054,81048            |

How can we achieve this? The SQL query below will tell you:

select proposal_id, item_ids
from (
    select proposal_id, group_concat(item_id) item_ids
    from the_table
    group by 1
    order by 1) z

The trick here is the GROUP_CONCAT() function. It returns a string with a concatenated non-NULL value from a group.

And here is the result:

| proposal_id | item_ids                |
|-------------|-------------------------|
| 1           | 83054,81048             |
| 2           | 71384,24282,19847,18482 |
| 3           | 84720                   |
| 4           | 18081,73018             |

You can play with the query here