MySQL Forums :: General :: Pivot tables


Advanced Search

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.

Options: ReplyQuote


Subject Written By Posted
Pivot tables L M 05/02/2012 03:17PM
Re: Pivot tables Peter Brawley 05/02/2012 04:55PM
Re: Pivot tables L M 05/02/2012 04:59PM
Re: Pivot tables Peter Brawley 05/03/2012 09:53AM
Re: Pivot tables haydn flower 05/03/2012 06:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.