让你蛋疼的行转列

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

SQL code?

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

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?

1 2 3 4 5 6 7 8 9 10 11

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?

1 2 3 4 5

--看到生成的代码 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?

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

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; 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　　                    select cartype,biusinesstypename ,   sum(case when biusinesstypename like '%车桥%' then 1 else 0 end) as 车桥,   sum(case when biusinesstypename like '%电瓶发动机%' then 1 else 0 end) as 电瓶发动机,    sum(case when biusinesstypename like '%空调%' then 1 else 0 end) as 空调,      sum(case when biusinesstypename like '%变速箱%' then 1 else 0 end) as 变速箱,     sum(case when biusinesstypename like '%燃油发动机%' then 1 else 0 end) as 燃油发动机,        sum(case when biusinesstypename like '%轮胎%' then 1 else 0 end) as 轮胎,    sum(case when biusinesstypename like '%委改故障%' then 1 else 0 end) as 委改故障,   sum(case when biusinesstypename like '%底盘%' then 1 else 0 end) as 底盘,   sum(case when biusinesstypename like '%电器线路%' then 1 else 0 end) as 电器线路,  sum(case when biusinesstypename like '%其他%' then 1 else 0 end) as 其他  from sxzq_failure  where  sxzq_failure.cartype is not null  group by cartype,biusinesstypename     create or replace procedure proc_Failure_Type as   viewname  varchar2(50):='v_tmp';   tabList varchar2(50):='sxzq_failure';    tabType varchar2(50):='sxzq_bussinesstype';   ListName varchar2(50):='cartype';   TypeName varchar2(50):='typename';   TypeName2 varchar2(50):='biusinesstypename';   TiaoJian varchar2(50):=' where path like ''%.22.%'' ';   sqlstr varchar2(2000):='create or replace view '||viewname||' as select '||ListName||' ';   c1 sys_refcursor;   v1 varchar2(100); begin   open c1 for 'select distinct to_char('||TypeName||') from '||tabType ||TiaoJian ;   loop     fetch c1 into v1;     exit when c1%notfound;     sqlstr:=sqlstr||',sum(case when '||TypeName2||' like ''%'||v1||'%''then 1 else 0 end) '||v1;   end loop;   close c1;   sqlstr:=sqlstr||' from '||tabList|| ' where '||ListName||' is not null'||' group by '||ListName;  execute immediate sqlstr; end proc_Failure_Type;