行列转换通用过程
来源:互联网 发布:mysql怎么优化sql语句 编辑:程序博客网 时间:2024/05/16 17:30
1.使用视图
create or replace procedure row_to_col(tabname in varchar2,
group_col in varchar2,in varchar2,
value_col in varchar2,
Aggregate_func in varchar2 default 'max',
colorder in varchar2 default null,
roworder in varchar2 default null,
column_col
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;
参数:
tabname 需要进行行转列操作的表名;
group_col 查询结果要按某列或某些列分组的字段名;
column_col 要从行转成列的字段;
value_col 需要聚合的值字段;
Aggregate_func 选用的聚合函数,可选,默认为max;
colorder 行转列后列的排序,可选;
roworder 行转列后记录的排序,可选;
when_value_null 若value_col字段的值聚合后为空,则转换成该值,可选;
viewname 创建的视图名称,可选,默认为v_tmp。
举例:
--测试数据
create table rowtocol_test as
select 2009 year,1 month,'部门1' dept,50000 expenditure from dual
union all select 2009,2,'部门1',20000 from dual
union all select 2009,2,'部门1',30000 from dual
union all select 2010,1,'部门1',35000 from dual
union all select 2009,2,'部门2',40000 from dual
union all select 2009,3,'部门2',25000 from dual
union all select 2010,2,'部门3',60000 from dual
union all select 2009,2,'部门3',15000 from dual
union all select 2009,2,'部门3',10000 from dual;
我现在想根据year和month分组,将部门转成列。
- SQL code
- SQL> select * from rowtocol_test; YEAR MONTH DEPT EXPENDITURE---------- ---------- ----- ----------- 2009 1 部门1 50000 2009 2 部门1 20000 2009 2 部门1 30000 2010 1 部门1 35000 2009 2 部门2 40000 2009 3 部门2 25000 2010 2 部门3 60000 2009 2 部门3 15000 2009 2 部门3 10000 9 rows selected SQL> execute row_to_col('rowtocol_test','year,month','dept','expenditure');PL/SQL procedure successfully completed SQL> select * from v_tmp; YEAR MONTH 部门1 部门3 部门2---------- ---------- ---------- ---------- ---------- 2009 1 50000 2010 1 35000 2009 3 25000 2009 2 30000 15000 40000 2010 2 60000 SQL>
这个结果可能不是我们想要的,重新调用过程,使用几个可选参数
- SQL code
- SQL> execute row_to_col('rowtocol_test','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',when_value_null => '0'); PL/SQL procedure successfully completed SQL> select * from v_tmp; YEAR MONTH 部门1 部门2 部门3---------- ---------- ---------- ---------- ---------- 2009 1 50000 0 0 2009 2 50000 40000 25000 2009 3 0 25000 0 2010 1 35000 0 0 2010 2 0 0 60000 SQL>
进行行转列的也可以是视图
- SQL code
- SQL> create view view_rowtocol as select * from rowtocol_test where year=2009; View created SQL> execute row_to_col('view_rowtocol','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',when_value_null => '0'); PL/SQL procedure successfully completed SQL> select * from v_tmp; YEAR MONTH 部门1 部门2 部门3---------- ---------- ---------- ---------- ---------- 2009 1 50000 0 0 2009 2 50000 40000 25000 2009 3 0 25000 0 SQL>
-----------------------------------------------------------
2.稍加修改,使用函数,返回游标。或利用过程里的传出参数
create or replace function row_to_col_func(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
)return sys_refcursor
Authid Current_User
as
sqlstr varchar2(2000):='select '||group_col||' ';
c1 sys_refcursor;
v1 varchar2(100);
cur sys_refcursor;
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;
open cur for sqlstr||' from '||tabname||' group by '||group_col||case when roworder is not null then ' order by '||roworder end;
return cur;
end row_to_col_func;
在pl/sql dev中可以在sql窗口执行,查看结果
select
row_to_col_func('rowtocol_test','year,month','dept','expenditure',Aggregate_func => 'sum',colorder => 'dept',roworder => '1,2',when_value_null => '0')
from dual;
ROW_TO_COL_FUNC('ROWTOCOL_TEST
<Cursor>
YEAR MONTH 部门1 部门2 部门3
2009 1 50000 0 0
2009 2 50000 40000 25000
2009 3 0 25000 0
2010 1 35000 0 0
2010 2 0 0 60000
- 行列转换通用过程
- ORACLE 行列转换通用存储过程,已经转换的MSSQL版本
- 行列转换及存储过程分页
- Oracle 行列转换(存储过程)-mark
- (转)行列互换的通用存储过程
- 行列转换
- 行列转换
- 行列转换
- 行列转换
- 行列转换
- 行列转换
- 行列转换
- 行列转换
- 行列转换
- 行列转换
- 行列转换
- 行列转换
- 行列转换
- 运动的小企鹅[思路 过程说明.]完成...
- hql处理count sum 产生别名的错误
- sturts2 输出标签
- 3689 浮云
- 真无聊啊
- 行列转换通用过程
- 有些东西从看的明到看的有感觉了,层次可能就不一样了。
- javaWEB项目session值为空
- CPUFREQ驱动使用方法说明
- http://acm.hdu.edu.cn/showproblem.php?pid=1709
- Java异常的奇怪问题
- 基于V4L2的视频驱动开发(2)
- Fck editor
- 多线程编程(12) - 多线程同步之 Semaphore (信号对象)