ORACLE行转列通用过程

1.使用视图

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

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。

举例：

1 2 3 4 5 6 7 8 9 10 11

--测试数据 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分组，将部门转成列。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

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>

这个结果可能不是我们想要的，重新调用过程，使用几个可选参数

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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>

进行行转列的也可以是视图

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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.稍加修改，使用函数，返回游标。或利用过程里的传出参数

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28

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窗口执行，查看结果

1 2 3 4 5 6 7 8 9 10 11 12 13

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

 

原文：http://bbs.csdn.net/topics/330039676