I lived for a long time without knowing about this function, but it is really great when you need to create quick reports.
E.g. imagine that you have three tables:
Users table:
+----+------------------+ | id | email | +----+------------------+ | 1 | first@email.com | | 2 | second@email.com | | 3 | third@email.com | +----+------------------+
Clubs table:
+----+-------------+ | id | name | +----+-------------+ | 1 | Musicians | | 2 | Artists | | 3 | Programmers | +----+-------------+
And a many-to-many relationship table:
+---------+----------+ | user_id | club_id | +---------+----------+ | 1 | 1 | | 1 | 2 | | 2 | 2 | | 2 | 3 | | 3 | 1 | | 3 | 2 | | 3 | 3 | +---------+----------+
What if you needed to see which user belongs to which group, in a human readable format? You could write a query like:
SELECT user.email, club.name AS club_name FROM user INNER JOIN user_club ON (user.id = user_club.user_id) INNER JOIN club ON (club.id = user_club.club_id)
But the result will be messy (though easy to parse for a program afterwards):
+------------------+-------------+ | email | club_name | +------------------+-------------+ | first@email.com | Musicians | | third@email.com | Musicians | | first@email.com | Artists | | second@email.com | Artists | | third@email.com | Artists | | second@email.com | Programmers | | third@email.com | Programmers | +------------------+-------------+
If you want to have an instantly human-readable result, you can utilize GROUP_CONCAT() like this:
SELECT user.email, GROUP_CONCAT(club.name) AS clubs FROM user INNER JOIN user_club ON (user.id = user_club.user_id) INNER JOIN club ON (club.id = user_club.club_id) GROUP BY user.id
+------------------+-------------------------------+ | email | clubs | +------------------+-------------------------------+ | first@email.com | Musicians,Artists | | second@email.com | Artists,Programmers | | third@email.com | Musicians,Artists,Programmers | +------------------+-------------------------------+
Much nicer, isn’t it? Beware, this is not standard SQL and it won’t work in e.g. PostgreSQL, but you can utilize something like this in it:
SELECT user.email, array_to_string(array_agg(club.name), ',') FROM user INNER JOIN user_club ON (user.id = user_club.user_id) I NNER JOIN club ON (club.id = user_club.club_id) GROUP BY user.id