MySQL Forums
Forum List  »  General

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
May 02, 2012 03:17PM
May 02, 2012 04:55PM
L M
May 02, 2012 04:59PM
May 03, 2012 09:53AM
May 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.