Monday, April 20, 2009

MySQL GROUP_CONCAT

The GROUP_CONCAT function is used in MySQL to concatenate column data from a particular grouping into a string, rather than performing this action inside a client side script. For instance, if you have a table of large cities along with their state, you could do this:
SELECT state, city
FROM bigcities

which would return
+-------+--------------+
| state | city |
+-------+--------------+
| OH | Cleveland |
| OH | Columbus |
| OH | Cincinnati |
| PA | Pittsburgh |
| PA | Philadelphia |
...

and then process each row and group them by state within your PHP script. Or you could use GROUP_CONCAT as follows:
SELECT state, GROUP_CONCAT(city) AS cities
FROM bigcities
GROUP BY state

which would return:
+-------+--------------------------------+
| state | cities |
+-------+--------------------------------+
| OH | Cleveland,Columbus,Cincinnati |
| PA | Pittsburgh,Philadelphia |
...

The default separator is a comma, but you can define an alternative separator as follows:
SELECT state, GROUP_CONCAT(city SEPARATOR ' -- ') AS cities
FROM bigcities
GROUP BY state

which would return
+-------+--------------------------------------+
| state | cities |
+-------+--------------------------------------+
| OH | Cleveland -- Columbus -- Cincinnati |
| PA | Pittsburgh -- Philadelphia |
...

4 comments: