Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic display of columns in Pivot & Crosstab [merged 3]
icon5.gif   Dynamic display of columns in Pivot & Crosstab [merged 3] [message #383397] Wed, 28 January 2009 12:59 Go to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
I have a weekly and monthly score table.

Week_score
------------
Student_ID week_id month_id Year_id score


Month_score
------------
Student_ID month_id Year_id score


Month_score is made by aggregating week_score table by using a table 'month_weeks' which contains

month_id No_of_weeks



Suppose today is June 2009. The required output is

student_id 2007 2008 Jan09 Feb09 Mar09 APR09 MAY_1stweek May_2ndweek May_3rd Week May_4thWeek.


Is this possible?
Re: Dynamic Cross tab query [message #383399 is a reply to message #383397] Wed, 28 January 2009 13:05 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
Moderator , please remove the extra 2 messages , posted by me. The website was not responding . So i might have clicked the "create topic " button thrice.
Apologize for that.
Re: Dynamic Cross tab query [message #383404 is a reply to message #383397] Wed, 28 January 2009 15:12 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Post DDL for tables.
Post DML for test data.

Post expected/desired results.

Re: Dynamic Cross tab query [message #383421 is a reply to message #383404] Wed, 28 January 2009 17:24 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
I have created the DMLS and DDLs for the table and simplified the query requirement.

Please have a look.
Re: Dynamic Cross tab query [message #383428 is a reply to message #383397] Wed, 28 January 2009 19:23 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
characters between single quote marks are strings
'this is a string; 01/03/2009 not a DATE'

Search for "PIVOT query"
Re: Dynamic Cross tab query [message #383680 is a reply to message #383428] Thu, 29 January 2009 12:02 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
I have updated the DML . I m trying my best , but not getting a clue.
icon4.gif  Dynamic display of columns in Pivot query [message #383694 is a reply to message #383397] Thu, 29 January 2009 14:30 Go to previous messageGo to next message
konark
Messages: 24
Registered: February 2008
Location: Takhsila
Junior Member
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 attached.

Re: Dynamic display of columns in Pivot query [message #383768 is a reply to message #383694] Fri, 30 January 2009 00:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select roll_id,
  2         avg(decode(extract(year from mth_id),2007,score)) "2007avg",
  3         avg(decode(extract(year from mth_id),2008,score)) "2008avg",
  4         avg(decode(to_char(mth_id,'YYYYMM'),'200901',score)) Jan09,
  5         avg(decode(to_char(mth_id,'YYYYMM'),'200902',score)) Feb09,
  6         avg(decode(to_char(mth_id,'YYYYMM'),'200903',score)) Mar09
  7  from STUDENT_SCORE
  8  group by roll_id
  9  order by roll_id
 10  /
   ROLL_ID    2007avg    2008avg      JAN09      FEB09      MAR09
---------- ---------- ---------- ---------- ---------- ----------
       101       57.5       57.5       57.5
       102       57.5       57.5       57.5

2 rows selected.

Of course as all the values you provided for the date you asked are 57.5 the averages are 57.5.

Regards
Michel

[Updated on: Fri, 30 January 2009 00:52]

Report message to a moderator

Re: Dynamic display of columns in Pivot query [message #383839 is a reply to message #383694] Fri, 30 January 2009 11:03 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Check this link and see if it helps.

http://forums.oracle.com/forums/thread.jspa?messageID=1296899&#1297717

Regards

Raj
Previous Topic: Data Dictionary join issue
Next Topic: Select a row with a max value
Goto Forum:
  


Current Time: Fri Dec 09 17:14:19 CST 2016

Total time taken to generate the page: 0.25291 seconds