Posted By

cahyadsn on 07/25/12


mysql table pivot

Versions (?)

Who likes this?

1 person have marked this snippet as a favorite


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.

  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.