DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: cross tab

  1. #1
    Join Date
    Jan 2001
    Posts
    2,828

    Question

    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

  2. #2
    Join Date
    Feb 2001
    Posts
    49

    Thumbs up re:cross-ta

    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
    I love dba job

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width