chapter31 »

How many times have you wanted to return a list of grouped values from a database, in a single column, per row of your recordset? Actually I’ve wanted to do that a few times! Most of the time I’ll let the application layer handle that, but sometimes letting the database do the hard work fits the bill.

My not-so-theoretical example is course subjects (think English, Maths etc) that have 1-n “stages” (think categories) attached to them. If I want to return a recordset of all courses, with the attached stages, I might write a query like this:

1
2
3
4
5
6
7

SELECT sy.title as course, st.stage
FROM syllabus sy INNER JOIN syllabus_stages sy_st
ON sy.id = sy_st.syllabus_id
INNER JOIN stages st
ON sy_st.stage_id = st.id
GROUP BY sy.title, stage
ORDER BY sy.title

Looks pretty standard, with something like that I’d get the following:

But now the application has to handle the grouping, not a huge problem by any means. Also don’t get me wrong…this is where you normally would want to do this (sql is a data retrieval language and in most instances shouldn’t be handling presentation aspects). However, if I just wanted to display a list of stages (categories), I can use group_concat to make life a little easier.

1
2
3
4
5
6
7

SELECT sy.title as course, group_concat(st.stage ORDER BY st.stage) as stage
FROM syllabus sy INNER JOIN syllabus_stages sy_st
ON sy.id = sy_st.syllabus_id
INNER JOIN stages st
ON sy_st.stage_id = st.id
GROUP BY sy.title
ORDER BY sy.title

This gives me:

Pretty cool eh?

The default list separator is a comma, but you can also specify your own.

1
2
3
4
5
6
7

SELECT sy.title as course, group_concat(st.stage ORDER BY st.stage SEPARATOR ' | ') as stage
FROM syllabus sy INNER JOIN syllabus_stages sy_st
ON sy.id = sy_st.syllabus_id
INNER JOIN stages st
ON sy_st.stage_id = st.id
GROUP BY sy.title
ORDER BY sy.title

See the group_concat page for more details.