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.
Don’t miss the latest news about our development journey and SQL tutorials. Signup for our newsletter. Promise not to SPAM😎