Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2011
    Posts
    8

    Unanswered: Impossible Pivot?

    Hello everyone,

    (tldr down below)

    -- edit: Oracle 11g

    I have a problem with pivots ive been trying to figure out for quite a while now so I'm hoping someone here can help me:

    Pivots and how I use them:
    I want to show for week 01 to week 05 for this year, the profit all the people from sales made for every week seperately in columns:

    Code:
    select employee, week, profit
    from randomtable
    where week in ('201101','201102','201103','201104','201105')
    This will give me results as follow:
    Emp0001 201101 203847
    Emp0001 201103 27009
    Emp0003 201101 238022


    BUT I want it displayed like this:
    Employee | Week01 | Week02 | Week03 | Week04 | Week05
    data | data | data | data | data | data


    To achieve this I just take the previous query and add the pivot statement:

    Code:
    select * from (
    select employee, week, profit
    from randomtable
    where week in ('201101','201102','201103','201104','201105')
    ) pivot (max(profit) for week in ('201101','201102','201103','201104','201105'))
    MY PROBLEM
    Now having pivoted this statement (which was easy) I want to make the weeks dynamic, I want it to show the last 5 weeks (>sysdate-35) and also take this into the pivot. Now I cannot use a subquery or a range in the 'for week in ()' part of the pivot, so how can I solve this?


    TLDR:
    I want my pivot columns to be dynamic/be created from a subquery/range; how?
    Last edited by tcbenkhard; 01-25-12 at 07:00.

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Google is your besr friend.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Jan 2011
    Posts
    8
    Quote Originally Posted by LKBrwn_DBA View Post
    Google is your besr friend.
    Tx bro! You're no longer needed on this forum since you just solved all the problems here!


    Anyone with a brain and who's not an analphabetic that DOES have a solution?

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down

    Quote Originally Posted by tcbenkhard View Post
    Tx bro! You're no longer needed on this forum since you just solved all the problems here!

    Anyone with a brain and who's not an analphabetic that DOES have a solution?
    Anyone with a brain who is not a moron should know that this question has been asked and answered many times, you are just too lazy search, perhaps even you did not click on the link I posted.

    Last edited by LKBrwn_DBA; 01-25-12 at 17:23.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    whoa
    can we keep the personal abuse in check please guys
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by tcbenkhard View Post
    Anyone with a brain and who's not an analphabetic that DOES have a solution?
    What about the one which was given you by spacebar in your previous thread about similar topic: http://www.dbforums.com/oracle/16636...led-pivot.html ?

    In short: a function returning a cursor based on dynamic sql. Of course, your client have to be able to treat such a cursor. Otherwise, it (whatever it is) has to build that dynamic query itself.

    Alternatively, you may play with PIVOT XML option, as described e.g. in this article: http://www.oracle-base.com/articles/...tors_11gR1.php
    I hope you will be able to use your own brain to transfer the XML output to ordinary columns. Here is a clue: http://www.dbforums.com/oracle/16735...sing-help.html

Posting Permissions

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