How to concatenate multiple rows into a string with GROUP BY in MySQL?

You have this table table_a:

id	random_character
1	i
1	t
2	i
2	s
3	v
3	e
3	r
3	y
4	n
4	i
4	c
4	e

And now you want to group them by id, and concatenate the random_character into one string for each group. Here is the expected output:

id	out_put
1	it
2	is
3	very
4	nice

Use the GROUP_CONCATE function:

SELECT
    id,
    GROUP_CONCAT(random_character SEPARATOR '') AS out_put
FROM
    table_a
GROUP BY
    id;

This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.

• To eliminate duplicate values, use the DISTINCT clause:

SELECT
    id,
    GROUP_CONCAT(DISTINCT random_character SEPARATOR '') AS out_put
FROM
    table_a
GROUP BY
    id;

• To sort values in the result, use the ORDER BY clause:

SELECT
    id,
    GROUP_CONCAT(DISTINCT random_character ORDER BY random_character SEPARATOR '') AS out_put
FROM
    table_a
GROUP BY
    id;

The default maximum length of the result is 1024, but you can set the value higher by running this command:

SET [GLOBAL | SESSION] group_concat_max_len = val;

---

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.


Download TablePlus for Mac. It’s free anyway!

Not on Mac? Download TablePlus for Windows.