Hi Guys
Really hope you can help with an issue that I have. I use the code below to output the days of the current week (stored in a table called dates)
SELECT to_char(NEXT_DAY(SYSDATE,'MON')
+ case when to_char(SYSDATE,'D') in (6,7) then -1 else -8 end + rownum, 'DD-MM-YY') day
from dual
connect by level <= 5;
I use the code below to populate my table
create table my_data
(apt_id int,
app_date date,
app_start_time varchar2(5),
app_end_time varchar2(5),
app_class varchar2(50)
)
insert into my_data values(636, date '03-10-2016', '09.00', '09.45', 'Maths');
insert into my_data values(637, date '04-10-2016', '10.00', '10.30', 'Biology');
insert into my_data values(649, date '05-10-2016', '10.45', '11.00', 'Computers');
insert into my_data values(1136, date '05-10-2016', '16.15', '17.00', 'French');
insert into my_data values(647, date '06-10-2016', '09.00', '09.30', 'German');
insert into my_data values(1131, date '06-10-2016', '12.30', '13.45', 'Chemistry');
insert into my_data values(641, date '07-10-2016', '16.00', '17.00', 'Physics');
I use row number to partition my data based on date and id eg someone could have numerous classes in one day the earliest class will always have the lowest id
with temp as (
select apt_id, app_date,app_start_time, app_end_time, app_class, ROW_NUMBER() OVER (partition by app_date order by apt_id) as R
from my_data
)
I then use the code below to display my grid (date as rows and classes as columns)
select day, case when r = 1 Then app_start_time || ' - ' || app_class else ' ' End "Class 1", case when r = 2 Then app_start_time || ' - ' || app_class else ' ' End "Class 2"
from dates
left join temp on day = app_date
order by day;
This outputs
Day Class 1 Class 2
03-10-2016 09.00 - Maths
04-10-2106 10.00 - Biology
05-10-2016 10.45 - Computers
05-10-2016 16.15 - French
This code does not work correctly because on the 5th October there are two rows 1 for the 10.45 class and another for the 16.15 class.
I was hoping you could help me change this grid so that the dates become the column headers and the Class Numbers become the rows
eg
03-10-2016 04-10-2016 05-10-2016
Class 1 09.00 - Maths 10.00 - Biology 10.45 - Computers
Class 2 16.15 - French
Any help greatly appreciated
Thanks
Sounds like you need to do the pivot!
You're taking the values from app_date and converting them to columns. Thus app_date goes in the for clause and the list of dates you want to appear in the output go in the in clause.
The values for the rows are the concatenation of start_time and class. You need to aggregate these (even if you only expect one row). So place these in a min(). This gives a pivot clause like:
pivot (min(app_start_time || ' - ' || app_class)
for app_date in (
date'2016-10-03' as "03-10-2016",
date'2016-10-04' as "04-10-2016",
date'2016-10-05' as "05-10-2016")
)
To change the columns in your output add or remove dates from the in clause.
Oracle implicitly groups by any other columns in the source not in the pivot. So the results will be split by row_number() automatically!
This gives you:
create table my_data
(apt_id int,
app_date date,
app_start_time varchar2(5),
app_end_time varchar2(5),
app_class varchar2(50)
);
insert into my_data values(636, date '2016-10-03', '09.00', '09.45', 'Maths');
insert into my_data values(637, date '2016-10-04', '10.00', '10.30', 'Biology');
insert into my_data values(649, date '2016-10-05', '10.45', '11.00', 'Computers');
insert into my_data values(1136, date '2016-10-05', '16.15', '17.00', 'French');
insert into my_data values(647, date '2016-10-06', '09.00', '09.30', 'German');
insert into my_data values(1131, date '2016-10-06', '12.30', '13.45', 'Chemistry');
insert into my_data values(641, date '2016-10-07', '16.00', '17.00', 'Physics');
with rws as (
select app_date, app_start_time, app_class,
row_number() over (partition by app_date order by app_start_time) rn
from my_data
)
select 'Class ' || rn, "03-10-2016", "04-10-2016", "05-10-2016"
from rws
pivot (min(app_start_time || ' - ' || app_class)
for app_date in (
date'2016-10-03' as "03-10-2016",
date'2016-10-04' as "04-10-2016",
date'2016-10-05' as "05-10-2016")
);
'CLASS'||RN 03-10-2016 04-10-2016 05-10-2016
Class 1 09.00 - Maths 10.00 - Biology 10.45 - Computers
Class 2 16.15 - French
Note you need to double quote your column names if you want them to start with numbers. Also - is the data type of dates.day a varchar2? If get rid of the table and recreate it with the values as dates! Otherwise you're going to run into all sorts of implicit conversion problems.
If you want to know more about pivoting read
https://blogs.oracle.com/sql/entry/how_to_convert_rows_to