Rows into columns-数据库专栏,其他相关

精彩推荐

分类最新教程

分类热点教程

Rows into columns

作者：未知

日期：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)

相关文章：