declare
v_str varchar2(4000):='';
v_val varchar2(20);
cursor c_Test is
select distinct to_char(速度) from tb;
begin
v_str:='select No';
open c_Test;
Loop
fetch c_Test into v_val;
exit when c_Test%NotFound;
v_str := v_str || ',sum(decode(速度,' || v_val || ', 值, 0)) ' || v_val;
End Loop;
close c_Test;
v_str :=v_str||' from tb group by No';
dbms_output.put_line(v_str);
end;
v_str varchar2(4000):='';
v_val varchar2(20);
cursor c_Test is
select distinct to_char(速度) from tb;
begin
v_str:='select No';
open c_Test;
Loop
fetch c_Test into v_val;
exit when c_Test%NotFound;
v_str := v_str || ',sum(decode(速度,' || v_val || ', 值, 0)) ' || v_val;
End Loop;
close c_Test;
v_str :=v_str||' from tb group by No';
dbms_output.put_line(v_str);
end;
------------------------------------------------
我只知道用这种动态PL/SQL的方法,:)
如果速度固定只有0 20 50 100 150这几个值,可以:
select
No,
sum(decode(速度,'0',值,0)),
sum(decode(速度,'20',值,0)),
sum(decode(速度,'50',值,0)),
sum(decode(速度,'100',值,0)),
sum(decode(速度,'150',值,0))
from 表
group by No
sum(case when 速度 = 0 then 值 else 0 end) 0,
sum(case when 速度 = 20 then 值 else 0 end) 20,
sum(case when 速度 = 50 then 值 else 0 end) 50,
sum(case when 速度 = 100 then 值 else 0 end) 100,
sum(case when 速度 = 150 then 值 else 0 end) 150,
sum(case when 速度 = 200 then 值 else 0 end) 200
from 表
group by No
-- group by No 无合计
-- group by cube(No) 出现顶行合计
-- group by rollup(No) 出现底行合计
order by No
如果要实现动态SQL
declare sqlStr varchar2(4000);
begin
sqlStr:='.................';
execute immediate sqlStr;
end;动态生成sqlStr中的SQL串