MySQL: How To Concatenate Multiple Rows Into Single String

  • by

You can concatenate rows into a single string using GROUP_CONCAT method. GROUP_CONCAT method can be used as an aggregate function(like GROUP BY), which returns a string value. In other words, all you have to do is to call run the SELECT FROM statement followed by GROUP_CONCAT function, with the column name to concatenate as a parameter.

Example of using GROUP_CONCAT() function:

SELECT person_id, GROUP_CONCAT(hobbies SEPARATOR ', ') FROM peoples_hobbies GROUP BY person_id;

To avoid duplications just Add an DISTINCT tag:

SELECT person_id, GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

You can also sort the values inside the GROUP_CONCAT using ORDER BY:

SELECT person_id, GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ') FROM peoples_hobbies GROUP BY person_id;

You can read the full documentation about GROUP_CONCAT method here


Download dbHarbor for free to try SQL queries from this tutorial. ⚓️  dbHarbor is a native macOS tool with intuitive UI to manage SQLite databases.

dbHarbor: SQLite

Don’t miss the latest news about our development journey and SQL tutorials. Signup for our newsletter. Promise not to SPAM😎

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *