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:
select country, state, 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.