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

    Unanswered: The hellhole called 'pivot'

    So I have a complex (for me atleast) situation:

    Table Recruiting:
    Int id
    Varchar name

    Table Client:
    Int id
    Varchar Name
    int recruiting_id (FK to Recruiting.id)

    Imagine getting this output:
    Code:
    Name          |          No. Clients        
    Google                 50
    Facebook             192
    Newspaper           52
    I want all the rows in NAME to become columns... (independent on how many rows there are)

    I have looked into this but its really unclear because everyone uses different styles and names for variables etc.. Could someone apply a simple pivot on this to achieve this:

    Code:
    google       Facebook          Newspaper
       50              192                    52

  2. #2
    Join Date
    Jan 2011
    Posts
    8

    Justice!

    Bump for justice!

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    This is just an example of using the 'pivot' clause on a select statement:
    Code:
    declare
      pivot_sql           varchar2( 2000 ) :=
        'select *
          from  ( select owner, count(*) as owner_segment_count
                   from  dba_segments
                  group by owner )
         pivot  ( sum( owner_segment_count ) for owner in(';
    
      c_quote    constant varchar2(    1 )  :=  '''';
    
      cursor pivot_items_cur is
        select distinct owner as owner
         from  dba_segments;
      v_pivot_item        varchar2(   30 );
      v_first_pivot_item  boolean       := FALSE;
    
    begin
      dbms_output.enable(null);
    
      open pivot_items_cur;
      loop
        fetch pivot_items_cur into v_pivot_item;
        exit when pivot_items_cur%NOTFOUND;
    
        if v_first_pivot_item then
          pivot_sql  :=  pivot_sql || ',';
        end if;
    
        if not v_first_pivot_item then
          v_first_pivot_item  :=  TRUE;
        end if;
    
        pivot_sql := pivot_sql || c_quote || v_pivot_item || c_quote;
    
      end loop;
    
      pivot_sql  :=  pivot_sql || '))';
      dbms_output.put_line( pivot_sql );
    
      execute immediate pivot_sql;
    
    end;
    If you have something that can process XML you could use something like this:
    Code:
    select *
     from  ( select owner, count(*) as owner_segment_count
              from  dba_segments
             group by owner )
     pivot xml( sum( owner_segment_count ) as owner_segment_count for owner in( any ) )


  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Spacebar: why do you post all your code samples with this tiny font?

  5. #5
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    This is another example using SQL*Plus:
    Code:
    variable  owner_ref_cur    refcursor 
    declare 
      v_sql       varchar2( 32767 ); 
     begin 
       v_sql  :=  'select * 
                    from  ( select owner, count(*) as owner_segment_count 
                             from  dba_segments 
                            group  by owner ) 
                    pivot ( sum( owner_segment_count ) 
                     for owner in('; 
       for r in( select distinct owner 
                  from  dba_segments ) 
       loop 
         v_sql  :=  v_sql || '''' ||  r.owner || ''','; 
       end loop; 
    
       v_sql  :=  rtrim( v_sql, ',' ) || ' ) )'; 
       open :owner_ref_cur for v_sql; 
     end; 
     / 
    
    print owner_ref_cur

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    most here don't want to answer homework questions so we ask you to
    provide what sql you tried to run to get your results.

    anyways ...
    PHP Code:
    duckcreate table test (name varchar2(50), client_num number);
    Table created.

    duckinsert into test values ('Facebook',69);
    1 row created.

    duckinsert into test values ('Google',71);
    1 row created.

    duckcommit;
    Commit complete.

    duckselect max(decode(name,'Facebook',client_num,NULL)) as facebook,
      
    2  max(decode(name,'Google',client_num,NULL)) as google from test;

      
    FACEBOOK     GOOGLE
    ---------- ----------
            
    69         71 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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