Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421

    Unanswered: Pivot / Cross / ?? Whatever...

    OK... Well I am Lost in the world of Cross tables and Pivot tables....

    What's what and who's who? I dont know anymore.
    I want to do a simple old Access Crosstab query.

    Data like below
    Code:
    Name Year/Q Amount
    AAAA 2005/1 5
    AAAA 2005/1 6
    AAAA 2005/2 10
    BBBB 2005/1 2
    Should then be displayed as
    Code:
    Name 2005/1 2005/2
    AAAA     11     10
    BBBB      2
    We are Using Oracle 9.2.0 and i have resorted to using the "good old" sum(decode()) stuff, which works for a limited amount of time.
    Because as time goes by new quarters are needed. Is there a better (and dynamic way) of doing this?

    I know Oracle never was good at this, but?? Maybe?? Someone thought up a trick?

    I have resorted allready to dynamicaly creating a script that drops and creates a view (with them decodes) thusly adding in any new quarters.

    Thanks in advance,

    The Mailman

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Check this page; perhaps you'll find the answer there.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I have posted code for dynamically creating pivot queries on my blog.

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    also Tom Kyte wrote a nifty dynamic pivot query.
    search his site for pivot query.

    http://asktom.oracle.com
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Thank you all for the suggestions and I did have a look at each of them, but all seem to be variations on the Sum(decode()) or Max(Decode()) or Count(Decode()) theme. No real "one line of SQL" pivot solutions.

    Evenso I did note one solution using PL/SQL that, just for the heck of it, I will try out. For anyone intrested, it is here in AskTom
    The biggest difference beeing that I am now spooling a file to be executed, where as the PL/SQL doesnt need the extra file....

    Greets,

    The Mailman

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by namliam
    Thank you all for the suggestions and I did have a look at each of them, but all seem to be variations on the Sum(decode()) or Max(Decode()) or Count(Decode()) theme. No real "one line of SQL" pivot solutions.
    There's a reason for that: that's the only way to do it in Oracle. But one of my solutions comes close to your "one line" requirement:

    Code:
    SQL> select pivot.pivot_cursor('deptno','job','emp','1') from dual;
    
    PIVOT.PIVOT_CURSOR('
    --------------------
    CURSOR STATEMENT : 1
    
    CURSOR STATEMENT : 1
    
        DEPTNO    ANALYST      CLERK    MANAGER  PRESIDENT   SALESMAN
    ---------- ---------- ---------- ---------- ---------- ----------
            10                     1          1          1
            20          2          2          1
            30                     1          1                     4

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Yep that looks like the PL/SQL variant that I was revering to (in my case on AskTom...)

    One line to me, means... One line... No PL/SQL or whatever involved... Just 1 sql statement like one can do in Access...

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Read my lips: you can't do it without PL/SQL!

    Or go back to using Access if you can't deal with that

  9. #9
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    LOL

    Uhm sorry... but access cannot handle the amount of data I need to handle...
    And I want to do a "one of" (ie not use access for reporting purposes) report.

    Like I said in my original post:
    i have resorted to using the "good old" sum(decode()) stuff
    I have been doing it that way in 8.1 for ages, without problems or even worries. Recently we upped to 9.2 without any "formal" kind of training. And I was just wondering...

    Is there anything new to 9.2 vs 8.1.7 that I should know? I have noticed for example that some queries seem to run much slower....
    Is the optimiser screwed/tweaked?
    Or are there other options like max memery usage for users that a DBA can set?
    Any other reasons (other than Index issues) that could be the cause of slow queries? FYI: Both queries and database did not change. It was just upgraded to 9.2.

    Greets

  10. #10
    Join Date
    Jul 2003
    Posts
    2,296
    doesn't sound like you read the 8i --> 9i upgrade documentation.

    make sure you are at an updated stable patch like 9.2.0.7
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  11. #11
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Nope, did not read the docs. Do not get the time to read them from my employer, alltho RTFM is a well known word around here.

    9.2.0.6 is our current patch

Posting Permissions

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