MySQL – Concatenate column values for each group with GROUP_CONCAT function

As I said in my earlier posts, MySQL has some interesting features that make the things simpler. GROUP_CONCAT is one of such functions

This function concatenates values from several rows into a single row separated by the delimiter specified.

For example consider the following example with simple data set

create table customer_visits(customer_id int, place_name varchar(100));

insert into customer_visits (customer_id,place_name)
select 1,'Chennai' union all
select 1,'Denmark' union all
select 1,'USA' union all
select 2,'UK' union all
select 2,'Argentina'

Now for each customer id, if you want to concatenate the visited places in a single string separated by comma, you can use the following code

select customer_id,
       GROUP_CONCAT(place_name) as visited_places
from
       customer_visits
group by
       customer_id

The result is

customer_id      visited_places
1                Chennai,Denmark,USA
2                UK,Argentina

Note that by default values are concatenated using comma. If you want to use different delimiter, say for example a semicolon (;), you can use SEPARATOR clause to do it as shown below.

select customer_id,
       GROUP_CONCAT(place_name SEPARATOR ';') as visited_places
from
       customer_visits
group by
      customer_id

The result is

customer_id      visited_places
1                Chennai;Denmark;USA
2                UK;Argentina

GROUP_CONCAT is really a quite handy function.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s