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.