1 2 Previous Next 18 Replies Latest reply: Dec 6, 2012 8:53 AM by Ashu_Neo RSS

    PIVOT

    Rahul_India
      My table and dml
      create table fish ( 
      fish_id number, 
      fish_type varchar2(3), 
      fish_weight number);
      
      insert into fish values (1,'COD',20); 
      insert into fish values(1,'HAD',30); 
      insert into fish values(2,'COD',45); 
      insert into fish values(2,'HKE',10); 
      insert into fish values(2,'LIN',55); 
      insert into fish values(3,'CTY',90); 
      insert into fish values (3,'HAD',60); 
      insert into fish values (3,'COD',52);
      I want to dis[play the data as  followING  using PIVOT
      {code}
      FISH_ID COD HAD HKE LIN CTY
      —————————— ————————— ————————— —————————— ————————— —————————
      1 20 30
      2 45 10 55
      3 52 60 90
      Can i do it using PIVOT .11G
      
      
      
      Here is my try
      select * from ( select fish_type,fish_weight from fish)
      pivot( fish_weight
      for fish_type in ( 'COD' as cod, 'HAD' as had,'HKE' as hke,'LIN' as lin) )
      error:ORA-56902: expect aggregate function inside pivot operation



      select * from ( select fish_type,fish_weight from fish)
      pivot( sum(fish_weight)
      for fish_type in ( 'COD' as cod, 'HAD' as had,'HKE' as hke,'LIN' as lin) )

      dont get the desired output as expected :(
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          
        • 1. Re: PIVOT
          jeneesh
          You are not slecting fish_id, and it is not coming in the output..If you add FISH_ID in your select list, you will get the desired output..
          select * 
          from 
          ( 
            select fish_id,fish_type,fish_weight 
            from fish
          )
            pivot( 
                sum(fish_weight)
                  for fish_type in ( 
                                     'COD' as cod, 'HAD' as had,
                                     'HKE' as hke,'LIN' as lin,
                                     'CTY' as cty
                                   ) 
                  );
          
          
          FISH_ID COD HAD HKE LIN CTY
          ------- --- --- --- --- ---
                1  20  30             
                2  45      10  55     
                3  52  60          90 
          Edited by: jeneesh on Dec 6, 2012 4:36 PM
          Formatted
          • 2. Re: PIVOT
            Ashu_Neo
            Check this
            SQL> SELECT * FROM (SELECT FISH_ID,fish_type, fish_weight FROM fish)
              2  PIVOT ( max(fish_weight)
              3  FOR fish_type IN ('COD' "COD",'HAD' "HAD",'HKE' "HKE",'LIN' "LIN",'CTY' "CTY")
              4  )
              5  /
            
               FISH_ID        COD        HAD        HKE        LIN        CTY
            ---------- ---------- ---------- ---------- ---------- ----------
                     1         20         30
                     2         45                    10         55
                     3         52         60                               90
            NB:- You have to use aggregate function while using PIVOT. There is no other way around to skip it.
            So What function you have to use, that depends upon your requirements.

            Thanks!

            Edited by: Ashu_Neo on Dec 6, 2012 4:35 PM
            -- Added comment
            • 3. Re: PIVOT
              Rahul_India
              jeneesh wrote:
              select * from 
              ( select fish_id,fish_type,fish_weight from fish)
              pivot( sum(fish_weight)
              for fish_type in ( 'COD' as cod, 'HAD' as had,'HKE' as hke,'LIN' as lin) )
              I gt the result
              I did not include fish_id column.But can u explain your query please.How was it evaluated?
              Thanks
              Rahul
              • 4. Re: PIVOT
                jeneesh
                Rahul_India wrote:
                I did not include fish_id column.But can u explain your query please.How was it evaluated?
                Thanks
                Rahul
                In PIVOT, the grouping is happening based on the select list of the query - In your first query, if you check the output, it is clear that grouping is not happening on FISH_ID. That means, it is not selected..
                • 5. Re: PIVOT
                  Rahul_India
                  .
                  • 6. Re: PIVOT
                    Rahul_India
                    jeneesh wrote:
                    Rahul_India wrote:
                    I did not include fish_id column.But can u explain your query please.How was it evaluated?
                    Thanks
                    Rahul
                    In PIVOT, the grouping is happening based on the select list of the query - In your first query, if you check the output, it is clear that grouping is not happening on FISH_ID. That means, it is not selected..
                    So whatever column we include in inner select statement ..grouping happens on them?
                    • 7. Re: PIVOT
                      Ashu_Neo
                      Add on:- You have to use always a wrapper query to main select query to base table to use PIVOT from accessing a table table. Else it will not work.
                      • 8. Re: PIVOT
                        Rahul_India
                        Ashu_Neo wrote:
                        Add on:- You have to use always a wrapper query to main select query to base table to use PIVOT from accessing a table table. Else it will not work.
                        why did u inlude MAX?
                        • 9. Re: PIVOT
                          jeneesh
                          Ashu_Neo wrote:
                          Add on:- You have to use always a wrapper query to main select query to base table to use PIVOT from accessing a table table. Else it will not work.
                          Normally you will have to "wrap" - But it is not mandatory...For example, in this particular case, since all the columns in the tabl are used, you dont need any "Wrapping"
                           select * 
                            from fish
                            pivot( 
                                sum(fish_weight)
                                  for fish_type in ( 
                                                     'COD' as cod, 'HAD' as had,
                                                     'HKE' as hke,'LIN' as lin,
                                                     'CTY' as cty
                                                   ) 
                                  );
                          • 10. Re: PIVOT
                            jeneesh
                            Rahul_India wrote:
                            Ashu_Neo wrote:
                            Add on:- You have to use always a wrapper query to main select query to base table to use PIVOT from accessing a table table. Else it will not work.
                            why did u inlude MAX?
                            For the sample data you provided MAX will work, but you should not use taht if ID,TYPE combination is not unique..

                            ie, if a row like below is added, MAX will fail
                            insert into fish values(3,'CTY',10); 
                            • 11. Re: PIVOT
                              ranit B
                              For a database <11g, where PIVOT doesn't work... try this...
                              select 
                                  fish_id "FISH_ID", 
                                  sum(case when fish_type='COD' then fish_weight else NULL end) "COD",
                                  sum(case when fish_type='HAD' then fish_weight else NULL end) "HAD",
                                  sum(case when fish_type='HKE' then fish_weight else NULL end) "HKE",
                                  sum(case when fish_type='LIN' then fish_weight else NULL end) "LIN",
                                  sum(case when fish_type='CTY' then fish_weight else NULL end) "CTY"    
                              from fish
                              group by fish_id;
                              gives
                              1     20     30               
                              2     45          10     55     
                              3     52     60               90
                              Ranit B.
                              • 12. Re: PIVOT
                                Ashu_Neo
                                why did u inlude MAX?
                                See As I mentioned above, that you need to use an aggregate function( to avoid syntax error for pivot). So I found your is storing numbers, so it just strikes to MAX/MIN/SUM to define that.
                                In other case (as defined by Jeenesh), when you included fish_id then aggregation happened but for each record.
                                • 13. Re: PIVOT
                                  Rahul_India
                                  ranit B wrote:
                                  For a database <11g, where PIVOT doesn't work... try this...
                                  select 
                                  fish_id "FISH_ID", 
                                  sum(case when fish_type='COD' then fish_weight else NULL end) "COD",
                                  sum(case when fish_type='HAD' then fish_weight else NULL end) "HAD",
                                  sum(case when fish_type='HKE' then fish_weight else NULL end) "HKE",
                                  sum(case when fish_type='LIN' then fish_weight else NULL end) "LIN",
                                  sum(case when fish_type='CTY' then fish_weight else NULL end) "CTY"    
                                  from fish
                                  group by fish_id;
                                  gives
                                  1     20     30               
                                  2     45          10     55     
                                  3     52     60               90
                                  Ranit B.
                                  We can also use decode ... :p
                                  • 14. Re: PIVOT
                                    jeneesh
                                    Ashu_Neo wrote:
                                    See As I mentioned above, that you need to use an aggregate function( to avoid syntax error for pivot). So I found your is storing numbers, so it just strikes to MAX/MIN/SUM to define that.
                                    I think you missed the pivot query with SUM in his first post....
                                    1 2 Previous Next