MySQL in a Nutshell

Name

GROUP_CONCAT( )

Synopsis

GROUP_CONCAT([DISTINCT] expression[,  . . . ]

   [ORDER BY {unsigned_integer|column|expression}
   [ASC|DESC] [,column  . . . ]]
   [SEPARATOR character])

This function returns non-NULL values of a group concatenated together, separated by commas. It returns NULL if the group doesn’t contain non-NULL values. Duplicates are omitted with the DISTINCT flag. The ORDER BY clause for the function instructs MySQL to sort values before concatenating. Ordering may be based on an integer value, column, or expression. The sort order can be set to ascending with the ASC flag (default), or to descending with the DESC flag. To change the default separator of a comma, use the SEPARATOR flag followed by the preferred separator. This function is available as of Version 4.1 of MySQL.

SELECT item_nbr AS Item,
      GROUP_CONCAT(quantity) AS Quantities
   FROM orders
   GROUP BY item_nbr LIMIT 1;
+------+------------+
| Item | Quantities |
+------+------------+
| 100  | 7,12,4,8   |
+------+------------+

The results here are limited only to one item by the LIMIT clause. Notice that the quantities aren’t sorted—it’s the item numbers that are sorted by the GROUP BY clause. To sort the quantities within each field and to use a different separator, enter something like the following instead:

SELECT item_nbr AS Item, GROUP_CONCAT(quantity ORDER BY quantity ASC SEPARATOR '|') AS Quantities FROM table3 GROUP BY item_nbr; +------+------------+ | Item | Quantities | +------+------------+ | 100 | 4|7|8|12 | +------+------------+ ...