Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Dynamic display of columns in pivot query

682415Jan 29 2009 — edited Feb 3 2009
I have a table called STUDENT_SCORE . I need to display the avarage score /month for past 2 years and the monthly score till today' month.

So suppose this is April 2009. The report will look like

Roll_id 2007avg 2008avg Jan09 Feb 09 Mar09
101 80.9 70.9 89.7 56.8 90.9
102 70.9 23.9 87.2 90.0 76.8


I have tried a bit , but can not fix the display of monthly score dynamically.


the DDL/DML is as below


create table STUDENT_SCORE
(roll_id number,
mth_id date,
score number) ;


insert into STUDENT_SCORE values
(101, to_date('01/01/2006', 'mm/dd/yyyy') , 67.5);
insert into STUDENT_SCORE values
(101, to_date('02/01/2006', 'mm/dd/yyyy') , 77.5);
insert into STUDENT_SCORE values
(101, to_date('03/01/2006', 'mm/dd/yyyy') , 87.5);
insert into STUDENT_SCORE values
(101, to_date('04/01/2006', 'mm/dd/yyyy') , 27.5);
insert into STUDENT_SCORE values
(101, to_date('05/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('06/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('07/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('08/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('09/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('10/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('11/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('12/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('01/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('02/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('03/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('04/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('05/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('06/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('07/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('08/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('09/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('10/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('11/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('12/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('01/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('02/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('03/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('04/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('05/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('06/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('07/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('08/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('09/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('10/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('11/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('12/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(101, to_date('01/01/2009', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('01/01/2006', 'mm/dd/yyyy') , 67.5);
insert into STUDENT_SCORE values
(102, to_date('02/01/2006', 'mm/dd/yyyy') , 77.5);
insert into STUDENT_SCORE values
(102, to_date('03/01/2006', 'mm/dd/yyyy') , 87.5);
insert into STUDENT_SCORE values
(102, to_date('04/01/2006', 'mm/dd/yyyy') , 27.5);
insert into STUDENT_SCORE values
(102, to_date('05/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('06/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('07/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('08/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('09/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('10/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('11/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('12/01/2006', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('01/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('02/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('03/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('04/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('05/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('06/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('07/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('08/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('09/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('10/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('11/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('12/01/2007', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('01/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('02/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('03/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('04/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('05/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('06/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('07/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('08/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('09/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('10/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('11/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('12/01/2008', 'mm/dd/yyyy') ,57.5);
insert into STUDENT_SCORE values
(102, to_date('01/01/2009', 'mm/dd/yyyy') ,57.5);
This post has been answered by Frank Kulash on Feb 3 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 3 2009
Added on Jan 29 2009
18 comments
2,223 views