MySQL in a Nutshell, 2nd Edition

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 by a GROUP BY clause, separated by commas. The parameters for this function are included in the parentheses, separated by spaces, not commas. The function returns NULL if the group doesn’t contain non-NULL values.

Duplicates are omitted with the DISTINCT keyword. The ORDER BY clause instructs the function to sort values before concatenating them. Ordering may be based on an unsigned integer value, a column, or an expression. The sort order can be set to ascending with the ASC keyword (default), or to descending with DESC. To use a different separator from a comma, use the SEPARATOR keyword followed by the preferred separator.

The value of the system variable group_concat_max_len limits the number of elements returned. Its default is 1024. Use the SET statement to change the value. This function is available as of version 4.1 of MySQL.

As an example of this function, suppose that we wanted to know how many customers order a particular item. We could enter an SQL statement like this:

SELECT item_nbr AS Item,
GROUP_CONCAT(quantity) AS Quantities
FROM orders
WHERE item_nbr = 100
GROUP BY item_nbr;

+------+------------+
| Item | Quantities |
+------+------------+
|  100 | 7,12,4,8,4 | 
+------+------------+

Notice that the quantities aren’t sorted—it’s the item numbers ...