Pivot tables
Posted by:
L M
Date: May 02, 2012 03:17PM
Hello,
I am new in MySQL, so forgive me if my question is trivial.
I am trying write an SQL that will fetch data from 2 tables and display the results as a cross-table or pivot table.
1st table has two columns: ID and NAME and represents days of the week.
1-Monday
2-Tuesday etc
Second table is a class scedule where day of the week is represented as ID 1,2...7
The simple select presents the data with no problem.
select
w.NAME as 'weekday',
concat(sc.HOUR_START, ':', sc.MIN_START, ' ', sc.HSTART_AMPM, ' - ',
sc.HOUR_END, ':', sc.MIN_END, ' ', sc.HEND_AMPM) as 'classtime',
sc.CLASS_NAME as 'classname'
from class_sched sc, WEEKDAYS w
where sc.day_id=w.ID
order by classtime;
the result is:
weekday, classtime, classname
Monday, 06:00 PM - 07:00 PM, Boot Camp
Monday, 11:00 AM - 03:00 PM, Private Lessons
Tuesday, 11:00 AM - 03:00 PM, Private Lessons
What I need is to convert weekday into a column and display the rest in rows.
I tried several variations of sqls and i still get all of the data in every row.
Will appreciate an advice.
thanks.