Mysql how to flatten a result set

Question

I have a stored procedure that will return a list of ids. I want to return that list as a comma separated string, i.e. "1,2,3,4,5". I know I can do this using a cursor but is there an easier way to turn a resultset into a flattened string?

Accepted Answer

MySQL has the group_concat() aggregate function:

```
SELECT group_concat(some_column) FROM mytable;
```

Will return all `some_column` values from table joined by commas.

Caveat: Beware that the result is limited by the `group_concat_max_len` system variable, which defaults to only 1024 bytes! To avoid hitting this wall, you should execute this before running the query:

```
SET SESSION group_concat_max_len = 65536;
```

Or more, depending on how many results you expect. But this value cannot be larger than `max_allowed_packet`

Mysql how to flatten a result set

Solution

Problem