Pivot table schedule

from the Artful Common Queries page

You have a schedule table (period, day, subject, room) with a primary key period,day to avoid duplicate bookings. You wish to display the schedule as periods, subjects and rooms in rows, and days of the week in columns. 
SELECT 
  period,
  MAX(IF(day=1, CONCAT(subject,' ',room), '')) AS Mon,
  MAX(IF(day=2, CONCAT(subject,' ',room), '')) AS Tue,
  MAX(IF(day=3, CONCAT(subject,' ',room), '')) AS Wed,
  MAX(IF(day=4, CONCAT(subject,' ',room), '')) AS Thu,
  MAX(IF(day=5, CONCAT(subject,' ',room), '')) AS Fri
FROM schedule
GROUP BY period
MAX() chooses existing over blank entries, and GROUP BY lines everything up on the same row.

Last updated 22 May 2024