MySQL: Concatenate Multiple Rows From a Table Into One Field
Published on Feb 27, 2012 by Jamie MunroThis is an excellent little trick that will save some processing time by a server-side language having to loop through the data to achieve the same result. In this article, I am going to demonstrate how you can merge multiple rows into one field separated by a specific character.
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:
A typical SQL query to retrieve these values would be done as follows:
SELECT interest FROM user_interests WHERE user_id = 5;
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;
By utilizing the existing GROUP_CONCAT function with MySQL, you can easily group multiple rows together into a single field result.