MySQL: GROUP_CONCAT

By admin | August 24, 2014

0 Comment

MySQL GROUP_CONCAT Function
In this tutorial, we will learn how to use the MySQL GROUP_CONCAT function to concatenate strings from a group of values with various options.
The following illustrates the GROUP_CONCAT function:

MySQL

1	GROUP_CONCAT(DISTINCT expression ORDER BY {column_name | usinged_integer | expression} SEPARATOR separator);

The maximum length of the return string is 1024 by default.
You can extend the returned value’s length by setting the group_concat_max_len system variable at SESSION or GLOBAL level.
MySQL GROUP_CONCAT Examples
Let’s take a look at the countries table in the sample database.

MySQL

1 2 3 4 5 6 7 8 9

CREATE TABLE `countries` ( `country_id` INT(10) NOT NULL AUTO_INCREMENT, `country_name` VARCHAR(100) NULL DEFAULT NULL, `status` ENUM('Y','N') NULL DEFAULT 'Y', PRIMARY KEY (`country_id`) ) ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=1

To get all countries as a (,) string, you use the GROUP_CONCAT function as follows:

MySQL

1	SELECT GROUP_CONCAT(country_name) as country_list FROM countries;

To remove the duplicate country names, you have to add the DISTINCT clause as the following query:

MySQL

1	SELECT GROUP_CONCAT(DISTINCT country_name) as country_list FROM countries;

To sort the country names before concatenating other than ASC, you have to use the ORDER BY clause as follows:

MySQL

1	SELECT GROUP_CONCAT(country_name ORDER BY country_name DESC) AS country_list FROM countries;

To change the default separator of the returned string from a comma (,) to a any other, you have to use the SEPARATOR clause as the following query:

MySQL

1	SELECT GROUP_CONCAT(DISTINCT country_name ORDER BY country_name DESC SEPARATOR ', ') AS country_list FROM countries;

Let’s take a look at the states table associated with countries table:

MySQL

1 2 3 4 5 6 7 8 9 10

CREATE TABLE `states` ( `state_id` INT(10) NOT NULL AUTO_INCREMENT, `state_name` VARCHAR(100) NOT NULL, `country_id` INT(10) NOT NULL, `status` ENUM('Y','N') NOT NULL DEFAULT 'Y', PRIMARY KEY (`state_id`) ) ENGINE=InnoDB ROW_FORMAT=DEFAULT AUTO_INCREMENT=1

To get country name and all associated state names as concatenated string using the following query:

MySQL

1 2 3 4

SELECT country.country_name,GROUP_CONCAT(state.state_name SEPARATOR ', ') as state_list FROM states AS state INNER JOIN countries AS country ON country.country_id = state.country_id GROUP BY state.country_id;

To get country name and all associated state names with their status(By using CONCAT function) as concatenated string using the following query:

MySQL

1 2 3 4

SELECT country.country_name,GROUP_CONCAT(IF(state.status = 'Y', CONCAT(state.state_name, '( Active)'),  CONCAT(state.state_name, '( Inactive)')) ORDER BY state.state_name ASC SEPARATOR ', ') as state_list FROM states AS state INNER JOIN countries AS country ON country.country_id = state.country_id GROUP BY state.country_id;

To get country name and all associated state names with their status(By using CONCAT_WS function) as concatenated string using the following query:

MySQL

1 2 3 4

SELECT country.country_name,GROUP_CONCAT(CONCAT_WS(', ', state.state_name, IF(state.status = 'Y','Active','Inactive')) ORDER BY state.state_name ASC SEPARATOR ', ') as state_list FROM states AS state INNER JOIN countries AS country ON country.country_id = state.country_id GROUP BY state.country_id;

Post Views: 6,492