oracle 行转列的通用过程

 原文传送门：http://topic.csdn.net/u/20091019/11/67cd55a3-3f42-4db7-a3f8-91dd52a913cd.html?24122

经常遇到发帖求行列转换的代码，用max(decode(..))回复后，十有八九会再问一句：如果列名不固定，或者列数不固定怎么办。就要用存储过程来写，这些存储过程的代码都大同小异，我就想能不能写个通用点的过程
试了一下，把结果发出来

SQL code

   
   

    
    
    
    create
    
     
    
    or
    
     
    
    replace
    
     
    
    procedure
    
     
    
    proc
    
    (tabname 
    
    in
    
     
    
    varchar2
    
    ,
                                  col1 
    
    in
    
     
    
    varchar2
    
    ,
                                  col2 
    
    in
    
     
    
    varchar2
    
    ,
                                  col3 
    
    in
    
     
    
    varchar2
    
    ,
                                  viewname 
    
    in
    
     
    
    varchar2
    
     
    
    default
    
     
    
    '
    
    v_tmp
    
    '
    
    )

    
    as
    
    
  sqlstr 
    
    varchar2
    
    (
    
    2000
    
    ):
    
    =
    
    '
    
    create or replace view 
    
    '
    
    ||
    
    viewname
    
    ||
    
    '
    
     as select 
    
    '
    
    ||
    
    col1
    
    ||
    
    '
    
     
    
    '
    
    ;
  c1 sys_refcursor;
  v1 
    
    varchar2
    
    (
    
    100
    
    );

    
    begin
    
    
  
    
    open
    
     c1 
    
    for
    
     
    
    '
    
    select distinct to_char(
    
    '
    
    ||
    
    col2
    
    ||
    
    '
    
    ) from 
    
    '
    
    ||
    
    tabname;
  loop
    
    
    fetch
    
     c1 
    
    into
    
     v1;
    
    
    exit
    
     
    
    when
    
     c1
    
    %
    
    notfound;
    sqlstr:
    
    =
    
    sqlstr
    
    ||
    
    '
    
    
  ,max(decode(
    
    '
    
    ||
    
    col2
    
    ||
    
    '
    
    ,
    
    '''
    
    ||
    
    v1
    
    ||
    
    '''
    
    ,
    
    '
    
    ||
    
    col3
    
    ||
    
    '
    
    ))"
    
    '
    
    ||
    
    v1
    
    ||
    
    '
    
    "
    
    '
    
    ;
  
    
    end
    
     loop;
  
    
    close
    
     c1;
  sqlstr:
    
    =
    
    sqlstr
    
    ||
    
    '
    
     from 
    
    '
    
    ||
    
    tabname
    
    ||
    
    '
    
     group by 
    
    '
    
    ||
    
    col1;
  
    
    execute
    
     immediate sqlstr;

    
    end
    
     
    
    proc
    
    ;
   
   

这里的几个参数，tabname指的是需要进行行列转换的表名，col1是这个表中行列转换以后要根据哪一列进行分组，那一列的列名。col2传入的是要将行转成列的那一列的列名，col3表示需要进行统计的数据列的列名
viewname传入希望建立的视图的名称，可以不填，默认为v_tmp
这么说很难让人明白..举个例子，引用一个帖子的数据

create table tab (
counter varchar(20),        -- 参加考试人数
subject varchar(20),        -- 科目
class varchar(20)          -- 班级
)


表数据：

counter  subject  class
36        英语      一班     
44        英语      二班
44        数学      二班
33        语文      一班
39        语文      三班


转换后：

      一班  二班  三班
英语  36    44    0
数学  0      44    0   
语文  33    0      39

编译好过程后，执行

SQL code

   
   

    
    
    
    begin
    
    

    
    proc
    
    (
    
    '
    
    tab
    
    '
    
    ,
    
    '
    
    subject
    
    '
    
    ,
    
    '
    
    class
    
    '
    
    ,
    
    '
    
    counter
    
    '
    
    );

    
    end
    
    ;


    
    --
    
    结果
    
    

    
    select
    
     
    
    *
    
     
    
    from
    
     v_tmp;

SUBJECT    一班    三班    二班
数学            
    
    44
    
    
英语    
    
    36
    
            
    
    44
    
    
语文    
    
    33
    
        
    
    39
    
        
   
   

如果对这个结果不是很满意，需要自己进行一些修改，比如空值的地方用0代替，或者需要用别的函数聚合而不是max。可以将过程中的execute immediate那句改成
dbms_output.put_line(sqlstr);
重新编译，执行，输出代码
如果用的是pl/sql dev的sql窗口，到output窗口查看

SQL code

   
   

    
    
    
    --
    
    看到生成的代码
    
    

    
    create
    
     
    
    or
    
     
    
    replace
    
     
    
    view
    
     v_tmp 
    
    as
    
     
    
    select
    
     subject 
  ,
    
    max
    
    (decode(class,
    
    '
    
    一班
    
    '
    
    ,counter))"一班"
  ,
    
    max
    
    (decode(class,
    
    '
    
    三班
    
    '
    
    ,counter))"三班"
  ,
    
    max
    
    (decode(class,
    
    '
    
    二班
    
    '
    
    ,counter))"二班" 
    
    from
    
     tab 
    
    group
    
     
    
    by
    
     subject
   
   

再加入nvl()，达到修改的目的

如果不想创建这样一个过程，则改成匿名块，需要时运行

SQL code

   
   

    
    
    
    declare
    
    
  tabname 
    
    varchar2
    
    (
    
    20
    
    ):
    
    =
    
    '
    
    XXX
    
    '
    
    ;
    
    --
    
    'XXX'分别用相应的表名和字段名代替
    
    

    
      col1 
    
    varchar2
    
    (
    
    10
    
    ):
    
    =
    
    '
    
    XXX
    
    '
    
    ;
  col2 
    
    varchar2
    
    (
    
    10
    
    ):
    
    =
    
    '
    
    XXX
    
    '
    
    ;
  col3 
    
    varchar2
    
    (
    
    10
    
    ):
    
    =
    
    '
    
    XXX
    
    '
    
    ;
  viewname 
    
    in
    
     
    
    varchar2
    
    (
    
    10
    
    ):
    
    =
    
     
    
    '
    
    v_tmp
    
    '
    
    ;
  sqlstr 
    
    varchar2
    
    (
    
    2000
    
    ):
    
    =
    
    '
    
    create or replace view 
    
    '
    
    ||
    
    viewname
    
    ||
    
    '
    
     as select 
    
    '
    
    ||
    
    col1
    
    ||
    
    '
    
     
    
    '
    
    ;
  c1 sys_refcursor;
  v1 
    
    varchar2
    
    (
    
    100
    
    );

    
    begin
    
    
  
    
    open
    
     c1 
    
    for
    
     
    
    '
    
    select distinct to_char(
    
    '
    
    ||
    
    col2
    
    ||
    
    '
    
    ) from 
    
    '
    
    ||
    
    tabname;
  loop
    
    
    fetch
    
     c1 
    
    into
    
     v1;
    
    
    exit
    
     
    
    when
    
     c1
    
    %
    
    notfound;
    sqlstr:
    
    =
    
    sqlstr
    
    ||
    
    '
    
    
  ,max(decode(
    
    '
    
    ||
    
    col2
    
    ||
    
    '
    
    ,
    
    '''
    
    ||
    
    v1
    
    ||
    
    '''
    
    ,
    
    '
    
    ||
    
    col3
    
    ||
    
    '
    
    ))"
    
    '
    
    ||
    
    v1
    
    ||
    
    '
    
    "
    
    '
    
    ;
  
    
    end
    
     loop;
  
    
    close
    
     c1;
  sqlstr:
    
    =
    
    sqlstr
    
    ||
    
    '
    
     from 
    
    '
    
    ||
    
    tabname
    
    ||
    
    '
    
     group by 
    
    '
    
    ||
    
    col1;
  
    
    --
    
    execute immediate sqlstr;
    
    

    
      dbms_output.put_line(sqlstr);

    
    end
    
    ;