Webistrate - Draw Your Own Conclusions

MySQL: Concatenate Multiple Rows From a Table Into One Field

Posted by Jamie Munro | February 27, 2012 | Tags:

Share

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.

Share


Related Posts

Leave a Reply

You must be logged in to post a comment.

Buy one of my books