Rows into columns

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)