Posted By

cahyadsn on 07/25/12

Tagged

mysql
table

Versions (?)

Last Edited at 07/25/12 01:43pm

Statistics

Viewed 2241 times

Favorited by 0 user(s)

Related snippets

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.

Expand | Embed | Plain Text

Copy this code and paste it in your HTML

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