Printable View
hiy folks
i am in need of help again .i would want 2 know about matrix reports or pivot tables in oracle 8.1.6 .i know thsi acn be done in acsses witha cross tab query.
also could anybody tell me about rollup and cube extensions of oracle 8i i read the oracle docs but couldnt make a thing of out of it.
thanx in advance
Hi hrishy,
Assume that we have a table called sales which appears as follows
SALES:-
----------
YEAR MONTH VALUE
----------------------
1997 3 100
1998 4 100
1999 5 100
2000 6 100
1997 6 100
1998 8 100
1999 4 100
----------------------
We have one record for each month of a every year. And if we want
to display the data for a year Horizontally, even though a record
exists for each month, it can be accomplished
using DECODE function.
SQL> Create table cross_tab1
(year number(4), month number(2), value number(12,2));
SQL> insert into cross_tab1 values (..values listed in SALES....);
View:-
-------
SQL> Select year,sum(decode(month,3,value,0)) March,
sum(decode(month,4,value,0)) April,
sum(decode(month,5,value,0)) May,
sum(decode(month,6,value,0)) June,
sum(decode(month,8,value,0)) August
from cross_tab1 group by year order by year;
Sample Output:-
-------------------
YEAR MAR APRIL MAY JUNE AUGUST
-------------------------------------------------------------------
1997 100 0 0 100 0
1998 0 100 0 0 100
1999 0 100 100 0 0
2000 0 0 0 100 0
----------------------------------------------------------------------
Check with your data and observe.
HTH.
Regards
VR.Murugappan
yeah answers my question but if any body would help me out with ROLLUP NAD CUBE functions which are new to 8i that would be great.
thanx 4 the help folks