select 姓名,max(decode(dk,1,成绩)), max(decode(dk,1,评定)), max(decode(dk,2,成绩)), max(decode(dk,2,评定)), max(decode(dk,3,成绩)), max(decode(dk,3,评定)) from (select 姓名,成绩,评定,dense_rank()over(order by 科目)dk from tt) group by 姓名
先谢谢wildwave但是科目动态的,会不断添加
select 姓名,max(decode(dk,1,成绩)), max(decode(dk,1,评定)), max(decode(dk,2,成绩)), max(decode(dk,2,评定)), max(decode(dk,3,成绩)), max(decode(dk,3,评定)) from (select 姓名,成绩,评定,dense_rank()over(order by 科目)dk from tt) group by 姓名这个是可以的
用临时表吧 declare v_count number; sqlstr varchar2(2000):='create global temporary table tmp on commit preserve rows as select name, ' ; i number:=0; begin for cur1 in (select distinct subject from student order by subject) loop
end loop; sqlstr:=substr(sqlstr,1,instr(sqlstr,',',-1)-1); sqlstr:=sqlstr||' from (select student.*,dense_rank()over(order by subject)dk from student ) group by name '; dbms_output.put_line(sqlstr); execute immediate sqlstr;
max(decode(dk,1,评定)),
max(decode(dk,2,成绩)),
max(decode(dk,2,评定)),
max(decode(dk,3,成绩)),
max(decode(dk,3,评定)) from
(select 姓名,成绩,评定,dense_rank()over(order by 科目)dk from tt)
group by 姓名
max(decode(dk,1,评定)),
max(decode(dk,2,成绩)),
max(decode(dk,2,评定)),
max(decode(dk,3,成绩)),
max(decode(dk,3,评定)) from
(select 姓名,成绩,评定,dense_rank()over(order by 科目)dk from tt)
group by 姓名这个是可以的
declare
v_count number;
sqlstr varchar2(2000):='create global temporary table tmp
on commit preserve rows as select name, ' ;
i number:=0;
begin
for cur1 in (select distinct subject from student order by subject) loop
i:=i+1;
sqlstr:=sqlstr||'nvl(max(decode(dk,'||i||',score)),0)'||cur1.subject||'成绩,
nvl(max(decode(dk,'||i||',evaluation)),''未评定'')'||cur1.subject||'评定,';
end loop;
sqlstr:=substr(sqlstr,1,instr(sqlstr,',',-1)-1);
sqlstr:=sqlstr||' from
(select student.*,dense_rank()over(order by subject)dk from student )
group by name
';
dbms_output.put_line(sqlstr);
execute immediate sqlstr;
end;
执行完以后运行select * from tmp
应该能得到你想要的结果
student代表你的表,subject。。等字段分别代表科目成绩评定等
自己修改下试试