Aggregate query

* base_table        student subject    grade
                    ------- ---------- -----
                    Fred    reading    A
                    Fred    writing    B
                    Fred    arithmetic C
                    Mary    reading    B
                    Mary    writing    B
                    Mary    arithmetic C

CREATE VIEW pivot_table AS
SELECT
    student,
    GROUP_CONCAT(CASE WHEN subject='reading' THEN grade ELSE NULL END) as reading,
    GROUP_CONCAT(CASE WHEN subject='writing' THEN grade ELSE NULL END) as writing,
    GROUP_CONCAT(CASE WHEN subject='arithmetic' THEN grade ELSE NULL END) as arithmetic,
FROM base_table
GROUP BY student;

* pivot_table
                    student reading writing arithmetic
                    ------- ------- ------- -------
                    Fred    A       B       C
                    Mary    B       B       C

/* What if you add a new subject?        */
/* Good for large result sets.           */
/* Performance for small results sets?   */