Oracle 存储过程实现不确定行列转置，不确定列就平均值

[size=large][b]create or replace procedure QSizeByStructId(
v_struct_id in number
)
authid current_user
is
strsql varchar2(4000);
str_CarModel varchar2(500);
str_create varchar(4000);
begin
strsql:='select re2.STRUCT_ID,re2.SIZE_ID,re2.MEASURE_SIZE,re2.SIZE_NAME,re2.NOTE,re2.RECOMMEND_A,re2.CAR_ID,
decode(re1.image,1,re1.measure_value||'';''||re1.size_car_id,re1.measure_value) measure_value ,cm.car_name||'';''||cm.car_id,re1.measure_value averagebysizeid
from size_car re1,car_model cm,
(select sii.struct_id,sii.size_id,sii.measure_size,sii.size_name,sii.note,sii.recommend_a,cid.car_id from sizes sii,
(select sc.car_id from sizes si,size_car sc where si.size_id=sc.size_id and
si.struct_id= '||v_struct_id||' group by sc.car_id
) cid where sii.struct_id=' ||v_struct_id|| ' order by cid.car_id,sii.size_id
) re2 where re1.size_id(+) = re2.size_id and re1.car_id(+) =
re2.car_id and re2.car_id=cm.car_id order by re2.car_id,re2.size_id';

str_create :='insert into temp_table '||strsql ;
execute immediate str_create;
commit;
end QSizeByStructId;


create or replace procedure row_col(
v_struct_id in number,
v_carmodel in varchar,
v_cirsor out sys_refcursor
) is
sqlstr varchar2(4000);
cursor mycursor is select ', max(decode(car_name,'||chr(39)||car_name||chr(39)||',measure_value,0))'||'"'||car_name||'" ' c2 from
temp_table group by car_name;
r1 mycursor%rowtype;
begin
QSIZEBYSTRUCTID(v_struct_id);
sqlstr :='select size_id ,measure_size,size_name,note,recommend_a,round(avg(averageBYSIZEID),2)';
open mycursor;
loop
fetch mycursor into r1;
exit when mycursor%notfound;
sqlstr:=sqlstr || r1.c2;

end loop;
close mycursor;
sqlstr:=sqlstr||' from temp_table group by (size_id,measure_size,size_name,note,recommend_a ) order by size_id ';
dbms_output.put_line(sqlstr);
open v_cirsor for sqlstr;
sqlstr:='delete from temp_table';
execute immediate sqlstr;
commit;
end row_col;[/b][/size]