23 Jan / 2013

Return MySQL query as a single string

I was playing with some statistics and wanted to display some queries as a single, comma-delimited text.

Why one may need this? Since the result of the queries can be concatenated in an endless ways by (eg. PHP) code… The answer: to have one-line query returning directly the string – eg. for statistical purposes only.

The data

Consider any type of data, for example:

CREATE TABLE `test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_bin NOT NULL,
`color` varchar(20) COLLATE utf8_bin NOT NULL,
`quantity` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`));

Insert some data…

INSERT INTO `test` (`id`, `name`, `color`, `quantity`) VALUES
(1, 'Orange', 'orange', 5),
(2, 'Apple', 'red', 25),
(3, 'Strawberry', 'red', 15),
(4, 'Banana', 'yellow', 12),
(5, 'Lemon', 'yellow', 1),
(6, 'Raspberry', 'red', 15),
(7, 'Raspberry', 'red', 15),
(8, 'Plum', 'blue', 5),
(9, 'Blueberry', 'blue', 25),
(10, 'Blue grapes', 'blue', 1),
(11, 'Grapes', 'green', 1),
(12, 'Kiwi', 'green', 5);

The solution: MySQL GROUP_CONCAT

To return any query as string, use the GROUP_CONCAT():

SELECT GROUP_CONCAT(`name`) FROM `test`
>> Orange,Apple,Strawberry,Banana,Lemon,Raspberry,Raspberry,Plum,Blueberry,Blue grapes,Grapes,Kiwi

To alter the order of the results:

Doesn't work:
SELECT GROUP_CONCAT(`name`) FROM `test` ORDER BY `name`;
>> Orange,Apple,Strawberry,Banana,Lemon,Raspberry,Raspberry,Plum,Blueberry,Blue grapes,Grapes,Kiwi

Does work:
SELECT GROUP_CONCAT(`name` ORDER BY `name`) FROM `test`;
>> Apple,Banana,Blue grapes,Blueberry,Grapes,Kiwi,Lemon,Orange,Plum,Raspberry,Raspberry,Strawberry

To enumerate distinct values:

SELECT GROUP_CONCAT(DISTINCT `color`) FROM `test`;
>> orange,red,yellow,blue,green

Ordering works like this:

SELECT GROUP_CONCAT(DISTINCT `color` ORDER BY `color`) FROM `test`;
>> blue,green,orange,red,yellow

Filtering

The WHERE clause must be specified at the end of the query, not inside the GROUP_CONCAT():

SELECT GROUP_CONCAT(DISTINCT `color`) FROM `test` WHERE `quantity`>10;
>> red,yellow,blue

Specify a separator

For practical purposes, you may want a different separator. Use the SEPARATOR clause inside the GROUP_CONCAT(). Notice the spaces around the dashes:

SELECT GROUP_CONCAT(DISTINCT `color` SEPARATOR ' - ') FROM `test` WHERE `quantity`>10;
>> red - yellow - blue

Documentation

You may want to read more about GROUP_CONCAT()… Visit the official documentation page at dev.mysql.com.

Leave a Reply

Your email address will not be published. Required fields are marked *

Blue Captcha Image
Refresh

*