powered by Slim Framework
enhanced by Nesbot.com

MySQL: Concatenate Multiple Rows From a Table Into One Field

Published on Feb 27, 2012 by Jamie Munro

This 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:

  • 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;


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;

Summary


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

Tags: Database | MySQL

<- CSS3: Creating a Transparent Background  Home CSS3: Rotating DOM Elements -> 
blog comments powered by Disqus