This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Dec 6, 2012 6:53 AM by Ashu_Neo RSS

PIVOT

Rahul_India Journeyer
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    .
  • 6. Re: PIVOT
    Rahul_India Journeyer
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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