This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Dec 6, 2012 6:53 AM by Ashu_Neo Go to original post RSS
  • 15. Re: PIVOT
    ranit B Expert
    Currently Being Moderated
    Ya Ya... of course...as you wish. I tried this instead of PIVOT coz i'm on 10g.
    DECODE is also coool ;-)
  • 16. Re: PIVOT
    Ashu_Neo Pro
    Currently Being Moderated
    Yep. OP might be have edited/formatted again before i had another look on main posting even yours posting too. :)
    But considering data in the table and output, is there any difference using max/min/sum ? I don't think so.
    using MAX will be failed.
     insert into fish values(3,'CTY',10);
    I disagreed. It'll not fail but give you the exact result as OP has asked (considering MAX value between 10 and 90 of same CTY group). But by using SUM it will add up thing( 10 + 90 = 100) for same group and will have different output. Like below
    SQL> ed
    Wrote file afiedt.buf
    
      1  SELECT * FROM (SELECT FISH_ID,fish_type, fish_weight FROM fish)
      2      PIVOT ( sum(fish_weight)
      3      FOR fish_type IN ('COD' "COD",'HAD' "HAD",'HKE' "HKE",'LIN' "LIN",'CTY' "CTY")
      4* )
    SQL> /
    
       FISH_ID        COD        HAD        HKE        LIN        CTY
    ---------- ---------- ---------- ---------- ---------- ----------
             1         20         30
             2         45                    10         55
             3         52         60                              100
    And I agreed , what you said about wrap a query to use PIVOT.

    Edited by: Ashu_Neo on Dec 6, 2012 7:33 PM
    -- Added comments and O/P
  • 17. Re: PIVOT
    jeneesh Guru
    Currently Being Moderated
    Ashu_Neo wrote:
    I disagreed. It'll not fail but give you the exact result as OP has asked
    Thats funny... :)

    What if I inserted 100 instead of 10...

    Expected output will change when data changes right?
    And i didnt mean fail, it will be wrong as op is askiing for sum...
  • 18. Re: PIVOT
    Ashu_Neo Pro
    Currently Being Moderated
    Expected output will change when data changes right?
    Off-course.You must be joking. ;):)
    And it's just an assumption. Op has not clearly mentioned that he really wanted to sum the things or just want to use an aggregate function in pivot function. Else he would have given data like that and O/P too; As you are assuming!
    The thing is , you read OP's mind fully and I partially.

    By the way, Thread is Closed..Jeenesh ;) :)
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points