Helping you solve those tough coding problems!

Concatenate Multiple Rows From a Table Into One Field with MySQL Concatenate Multiple Rows From a Table Into One Field with MySQL

Published on Feb 17, 2019 by Jamie Munro

When you want to merge multiple rows from a table into one field separated by a specific character using MySQL.

By utilizing the existing GROUP_CONCAT function with MySQL, you can easily group multiple rows together into a single field result.




To start, let's display an example. Let's assume we have users who have interests. These interests will be stored in a table called user_interests. In this example, the user with an id of 5 has the following interests:

  • Hockey

  • Football

  • Soccer



A typical SQL query to retrieve these values would be done as follows:
SELECT interest FROM user_interests WHERE user_id = 5;

Using GROUP_CONCAT



This would retrieve three separate rows. Now, what if I wanted to only return one row with all of the interests? This can be achieved with the MySQL GROUP_CONCAT function. The above query would be altered as follows:
SELECT GROUP_CONCAT(interest SEPARATOR ', ') as interests FROM user_interests WHERE user_id = 5;

The GROUP_CONCAT can also define an order of the concatenation. The above example can be altered as follows to order the interests alphabetically:
SELECT GROUP_CONCAT(interest ORDER BY interest ASC SEPARATOR ', ') as interests FROM user_interests WHERE user_id = 5;

Tags: SQL | mysql | GROUP_CONCAT

My Books
ASP.NET MVC 5 With Bootstrap and Knockout.js
Knockout.js Building Dynamic Client-Side Applications
20 Recipes for Programming MVC 3
20 Recipes for Programming PhoneGap
Rapid Application Development with CakePHP