MySQL group_concat()

The group_concat() function in MySQL is really nifty. It concatenates all rows that match your group by statement into a single, comma separated string. Essentially, it transposes rows to columns.

Take this example. Say you have a table that stores a set of locations by country, state and city.

You could then write SQL as follows:

  group_concat(city) as cities 
from locations 
group by country, state

What this is saying is bring back 3 columns, country, state, and for each combination of country and state, a list of all of the cities in that country and state as the third column (called cities).

This is actually a phenomenally handy thing to be able to do, and something that is much harder to do in any other DBMS.