行转列的通用过程

[小灰狼W]

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

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

编译好过程后，执行

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窗口查看

--看到生成的代码
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()，达到修改的目的

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

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;

p.s.我对上面的过程进行了改进，参阅http://topic.csdn.net/u/20100109/13/6a10c168-f190-4766-b838-adbf03c4ac7b.html

[无忧工作室]

学习，收藏

[voice007]

mark!

[hebo2005]

需要建视图啊
这样万一不断在变查询表，就会不断建视图，另外还需要用户有建视图的权限

[notebook800]

瞻仰

[tiancx82]

谢谢楼主，收藏了，帮助很大

[haixingfuyun]

看上去不错.,

[小灰狼W]

[Quote=引用 26 楼 hebo2005 的回复:]
需要建视图啊
这样万一不断在变查询表，就会不断建视图，另外还需要用户有建视图的权限
[/Quote]
建视图是为了方便查询
只要表结构和需求不变，不用重新建视图啊。而且重新执行过程会覆盖原来的视图
权限确实是的，要创建视图的权限，不过这个问题应该不大

[liguominz]

收藏了，谢谢。

[cougar0709]

辛苦了，收藏学习

[huangya0]

标记一下

[sjm5210]

好贴 我顶

[啵比]

看看在学习...!

[sxdgundam]

mark收藏！

[小灰狼W]

[Quote=引用 37 楼 jiasongmao850306 的回复:]
楼主用的哪个版本的数据库，我在SQL 2000里面运行怎么总是提示有很多错误呢？

[/Quote]
这是oracle的..如果要使用sqlserver需要进行一些修改

[bigzoom]

楼主用的哪个版本的数据库，我在SQL 2000里面运行怎么总是提示有很多错误呢？

[computer_2010]

学习！

[dzntree]

11g有个新特性，用PIVOT函数，以楼主的成绩表的例子，可以写成：

select * from tab
pivot(
      sum(counter) for class in ('one' as one,'two' as two,'three' as three)
      )
order by subject;

[dzntree]

请问楼主列转行如何做呢？？请教~~~

[ldzsl]

每天回帖即可获得10分可用分！小技巧

[baidiao]

我这里执行报错啊，哪位帮忙看看，错误截图如下：