之前发过一个帖子，叫行转列的通用过程，http://topic.csdn.net/u/20091019/11/67cd55a3-3f42-4db7-a3f8-91dd52a913cd.html能满足最基本的需求。但也有一些缺陷，现在对其进行完善
代码
1.使用视图
-
SQL code
-
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;
这里修改了传入参数名，使其更容易理解。继续使用了创建视图这个方法，当然也可以改成用游标传出。
参数：
tabname 需要进行行转列操作的表名；
group_col 查询结果要按某列或某些列分组的字段名；
column_col 要从行转成列的字段；
value_col 需要聚合的值字段；
Aggregate_func 选用的聚合函数，可选，默认为max；
colorder 行转列后列的排序，可选；
roworder 行转列后记录的排序，可选；
when_value_null 若value_col字段的值聚合后为空，则转换成该值，可选；
viewname 创建的视图名称，可选，默认为v_tmp。
举例：
-
SQL code
-
-- 测试数据 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.稍加修改，使用函数，返回游标。或利用过程里的传出参数
-
SQL code
-
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窗口执行，查看结果
-
SQL code
-
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
276
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
