1 2 Previous Next 18 Replies Latest reply: Dec 6, 2012 8:53 AM by Ashu_Neo Go to original post RSS
      • 15. Re: PIVOT
        ranit B
        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
          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
            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
              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