Rows into columns

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

create or replace package body pivotas                                                                                        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)                                                                                

原文转自：http://www.ltesting.net