create or replace package pivot
as
   type rc is ref cursor;
   procedure data ( p_cursor in out rc );
end;

create or replace package body pivot
as
                                                                                
      
procedure data( p_cursor in out rc )
is
    l_stmt long;
begin
                                                                                
      
    l_stmt := 'select tr_date';
    for x in ( select distinct item_id from t order by 1 )
    loop
        l_stmt := l_stmt ||
        ', max(decode(item_id,' || x.item_id ||
             ', adult )) adult_' || x.item_id ||
        ', max(decode(item_id,' || x.item_id ||
             ', child )) child_' || x.item_id;
    end loop;
    l_stmt := l_stmt || ' from t group by tr_date order by tr_date';
                                                                                
      
    open p_cursor for l_stmt;
end;

測試環境:

create table t (tr_date date, item_id number,adult number,child number)  

insert into t values(to_date('06/01/2004','DD/MM/YYYY') ,  9  ,  1199 ,   839.3)
insert into t values(to_date('06/01/2004','DD/MM/YYYY') ,   588  ,  1249  ,  874.3)
insert into t values(to_date('06/01/2004','DD/MM/YYYY') ,   4894  ,  2339 ,   2339)