Question

sql语句转换成oracle的存储过程

declare @sql varchar ( 8000 )
set @sql = ' select Name as ' + ' 姓名 '
select @sql = @sql + ' , max(case Subject when ''' + Subject + ''' then Result else 0 end) [ ' + Subject + ' ] '
from ( select distinct Subject from tb) as a
set @sql = @sql + ' from tb group by name '
exec ( @sql )
这样的语句，sql中行列转换的，谁能帮忙转换成oracle的存储过程，转换好200分

Answer 1

已经帮你转换好了
create or replace procedure xxx(x out sys_refcursor) as
n1 varchar2(4000);
n2 varchar2(100);
cursor c1
is
select distinct subject from tb;

begin
n1:='select name as 姓名';
open c1;
loop
fetch c1 into n2;
exit when c1%notfound;
n1:=n1||',max(case when subject='''||n2||''' then result else 0 end) as '||n2;
end loop;
close c1;
n1:=n1||' from tb group by name ';
OPEN x FOR n1;
end;
在sql plus中如下调用即可
var x refcursor;
exec ttt(:x);
print;
满意就多给点分哈，我也比较新手，调试了大半天才搞定。

追问

我想把结果集弄到一个表或者试图该怎么做呢？

Answer 2

select WMSYS.WM_CONCAT(col_name) from dual
oralce 9以上这个可以用.
不过大小有限制.自己试试看吧.

Answer 3

你把表结构会要实现的功能给出来吧