Oracle行转列通用过程

create or replace procedure row_to_col(tabname in varchar2,
                                  group_col
in varchar2, column_col in varchar2,
                                  value_col
in varchar2,
                                  Aggregate_func
in varchar2 default 'max',
                                  colorder
in varchar2 default null,
                                  roworder
in varchar2 default null, when_value_null in varchar2 default null,
                                  viewname
in varchar2 default 'v_tmp')
Authid
Current_User
as
  sqlstr
varchar2(2000):='create or replace view '||viewname||' as select '||group_col||' ';
  c1 sys_refcursor;
  v1
varchar2(100);
begin
 
open c1 for 'select distinct '||column_col||' from '||tabname||case when colorder is not null then ' order by '||colorder end;
  loop
   
fetch c1 into v1;
   
exit when c1%notfound;
    sqlstr:
=sqlstr||chr(10)||','||case when when_value_null is not null then 'nvl(' end||
      Aggregate_func
||'(decode(to_char('||column_col||'),'''||v1||''','||value_col||'))'||
     
case when when_value_null is not null then chr(44) ||when_value_null||chr(41) end||'"'||v1||'"';
 
end loop;
 
close c1;
  sqlstr:
=sqlstr||' from '||tabname||' group by '||group_col||case when roworder is not null then ' order by '||roworder end;
 
execute immediate sqlstr;
end row_to_col;

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值