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)
