Pivot table with CONCAT

from the Artful Common Queries page


Here is a MySQL pivot table query for room bookings by weekday:

SELECT slot
  , max(if(day=1, concat(subject,' ',room), '')) as day1
  , max(if(day=2, concat(subject,' ',room), '')) as day2
  , max(if(day=3, concat(subject,' ',room), '')) as day3
  , max(if(day=4, concat(subject,' ',room), '')) as day4
  , max(if(day=5, concat(subject,' ',room), '')) as day5
from schedule
group by slot

MAX(...) decides between an entry and a blank (the entry will win if one exists) while the group by lines everything up on the same row. Friendly caution: If more than one entry exists for the same day and time, you will only see the one that is alphabetically "greater".

To see how many classes are scheduled by day for each slot (to check for conflicts) try:

SELECT slot
  , sum(if(day=1,1,0)) as day1
  , sum(if(day=2,1,0)) as day2
  , sum(if(day=3,1,0)) as day3
  , sum(if(day=4,1,0)) as day4
  , sum(if(day=5,1,0)) as day5
from schedule
group by slot

There is a pattern:
  • Columns you want as "row headers" are listed both in the SELECT _and_ in the GROUP BY clauses
  • Values you want as columns are selectively chosen by IF() functions to return something or nothing so that one of the aggregate functions (MIN, MAX, SUM,AVG, etc) can condense those rows and columns into single values
What to count in any column can be as complex as you like. If you don't like the IF() function you can use CASE statements or anything else to help you provide the correct set of values for the aggregate function you are using on that column. You can mix and match aggregate functions to be what you need. Let's say you join the appropriate tables together to form a data set that looks like:

day, slot, subject, student, grade

and you wanted to build a chart showing the statistics of grades vs. days and subject. You could use this type of query to work that out:

SELECT day, subject
  , AVG(grade) as average
  , MIN(grade) as lowest
  , MAX(grade) as highest
from <necessary tables>
group by day, subject

Now while one needn't choose values for columns, to "pivot" that table by days (each column representing statistics for just one day) change the query to:

SELECT subject
  , AVG(IF(day=1, grade,null)) as D1_average
  , MIN(IF(day=1, grade, null)) as D1_lowest
  , MAX(IF(day=1,grade,null)) as D1_highest
  , AVG(IF(day=2, grade,null)) as D2_average
  , MIN(IF(day=2, grade, null)) as D2_lowest
  , MAX(IF(day=2,grade,null)) as D2_highest
  , .... (repeat for rest of the days)
FROM <necessary tables>
GROUP BY day, subject

The IF ... NULL test prevents AVG() from counting all other grades for the same subject from different days. The same trick works for MIN and MAX functions.

Return to the Artful Common Queries page