行转列通用过程

（转自狂浪的帖子）

 

1.使用视图SQL codecreateorreplaceprocedure row_to_col(tabnameinvarchar2,
                                  group_colinvarchar2,
                                  column_colinvarchar2,
                                  value_colinvarchar2,
                                  Aggregate_funcinvarchar2default'max',
                                  colorderinvarchar2defaultnull,
                                  roworderinvarchar2defaultnull,
                                  when_value_nullinvarchar2defaultnull,
                                  viewnameinvarchar2default'v_tmp')
AuthidCurrent_Useras
  sqlstrvarchar2(2000):='create or replace view'||viewname||' as select'||group_col||'';
  c1 sys_refcursor;
  v1varchar2(100);beginopen c1for'select distinct'||column_col||' from'||tabname||casewhen colorderisnotnullthen' order by'||colorderend;
  loopfetch c1into v1;exitwhen c1%notfound;
    sqlstr:=sqlstr||chr(10)||','||casewhen when_value_nullisnotnullthen'nvl('end||
      Aggregate_func||'(decode(to_char('||column_col||'),'''||v1||''','||value_col||'))'||casewhen when_value_nullisnotnullthen chr(44)||when_value_null||chr(41)end||'"'||v1||'"';end loop;close c1;
  sqlstr:=sqlstr||' from'||tabname||' group by'||group_col||casewhen roworderisnotnullthen' order by'||roworderend;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--测试数据createtable rowtocol_testasselect2009year,1month,'部门1' dept,50000 expenditurefrom dualunionallselect2009,2,'部门1',20000from dualunionallselect2009,2,'部门1',30000from dualunionallselect2010,1,'部门1',35000from dualunionallselect2009,2,'部门2',40000from dualunionallselect2009,3,'部门2',25000from dualunionallselect2010,2,'部门3',60000from dualunionallselect2009,2,'部门3',15000from dualunionallselect2009,2,'部门3',10000from dual;我现在想根据year和month分组，将部门转成列。
SQL codeSQL>select*from rowtocol_test;YEARMONTH DEPT  EXPENDITURE---------- ---------- ----- -----------20091 部门15000020092 部门12000020092 部门13000020101 部门13500020092 部门24000020093 部门22500020102 部门36000020092 部门31500020092 部门3100009 rows selected
 
SQL>execute row_to_col('rowtocol_test','year,month','dept','expenditure');

PL/SQLprocedure successfully completed
 
SQL>select*from v_tmp;YEARMONTH        部门1        部门3        部门2---------- ---------- ---------- ---------- ----------200915000020101350002009325000200923000015000400002010260000
 
SQL>
这个结果可能不是我们想要的，重新调用过程，使用几个可选参数
SQL codeSQL>execute row_to_col('rowtocol_test','year,month','dept','expenditure',Aggregate_func=>'sum',colorder=>'dept',roworder=>'1,2',when_value_null=>'0');
 
PL/SQLprocedure successfully completed
 
SQL>select*from v_tmp;YEARMONTH        部门1        部门2        部门3---------- ---------- ---------- ---------- ----------20091500000020092500004000025000200930250000201013500000201020060000
 
SQL>
进行行转列的也可以是视图
SQL codeSQL>createview view_rowtocolasselect*from rowtocol_testwhereyear=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/SQLprocedure successfully completed
 
SQL>select*from v_tmp;YEARMONTH        部门1        部门2        部门3---------- ---------- ---------- ---------- ----------20091500000020092500004000025000200930250000
 
SQL>
-----------------------------------------------------------
2.稍加修改，使用函数，返回游标。或利用过程里的传出参数SQL codecreateorreplacefunction row_to_col_func(tabnameinvarchar2,
                                  group_colinvarchar2,
                                  column_colinvarchar2,
                                  value_colinvarchar2,
                                  Aggregate_funcinvarchar2default'max',
                                  colorderinvarchar2defaultnull,
                                  roworderinvarchar2defaultnull,
                                  when_value_nullinvarchar2defaultnull
                                  )return sys_refcursor
AuthidCurrent_Useras
  sqlstrvarchar2(2000):='select'||group_col||'';
  c1 sys_refcursor;
  v1varchar2(100);
  cur sys_refcursor;beginopen c1for'select distinct'||column_col||' from'||tabname||casewhen colorderisnotnullthen' order by'||colorderend;
  loopfetch c1into v1;exitwhen c1%notfound;
    sqlstr:=sqlstr||chr(10)||','||casewhen when_value_nullisnotnullthen'nvl('end||
      Aggregate_func||'(decode(to_char('||column_col||'),'''||v1||''','||value_col||'))'||casewhen when_value_nullisnotnullthen chr(44)||when_value_null||chr(41)end||'"'||v1||'"';end loop;close c1;open curfor sqlstr||' from'||tabname||' group by'||group_col||casewhen roworderisnotnullthen' order by'||roworderend;return cur;end row_to_col_func;
在pl/sql dev中可以在sql窗口执行，查看结果
SQL codeselect
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