advanced code snippet search

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.

Expand | Embed | Plain Text 
  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.