Posted By

cahyadsn on 07/25/12


Tagged

mysql table pivot


Versions (?)

Pivot table Schedule


 / Published in: MySQL
 

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.

  1. SELECT
  2. period,
  3. MAX(IF(DAY=1, CONCAT(subject,' ',room), '')) AS Mon,
  4. MAX(IF(DAY=2, CONCAT(subject,' ',room), '')) AS Tue,
  5. MAX(IF(DAY=3, CONCAT(subject,' ',room), '')) AS Wed,
  6. MAX(IF(DAY=4, CONCAT(subject,' ',room), '')) AS Thu,
  7. MAX(IF(DAY=5, CONCAT(subject,' ',room), '')) AS Fri
  8. FROM schedule
  9. GROUP BY period

Report this snippet  

You need to login to post a comment.