Group and concatenate rows

By | 29 May, 2012

If we have a table like this:

id name year
1 museum 2 2012
2 museum 1 2012
3 museum 2 2010
4 museum 1 2011
5 museum 3 2012

And we want to list all the museums and on which year they..well, they did something.

Something like this result:

name year
museum 1 2012,2011
museum 2 2012,2010
museum 3 2012

We would go about it like this:

SELECT name,GROUP_CONCAT(year) AS year FROM museums GROUP BY name ORDER BY name ASC,year DESC

The trick is the group_concat. What this does is self explanatory, but still, it groups the same museums and concatenates the years (different rows on the query without grouping) it has done something. Yeah, great explaining of the obvious.

You can elaborate from here with power joins, oranges and pizza.