Rows into columns - 中國WEB開發者網絡 (http://www.webasp.net) -- 技術教程 (http://www.webasp.net/article/) --- Rows into columns (http://www.webasp.net/article/22/21193.htm)

-- 作者：未知 -- 發佈日期： 2005-04-29

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)

webasp.net