how to write this SQL statement

[York_Kwan]

the dataSet as this:
//---------------------------------------------
NAME SUBJECT TEAM SCORE 　//DataField
sinmax math first 60
myname math second 70
myname math third 80 　 　 　
myname english first 60 　 　 　
myname english second 70 　 　 　
myname english third 80 　 　
myname chinese first 60 　 　
myname chinese second 70 　 　
myname chinese third 80 　
//---------------------------------------------


after Excute the SQL sentence,
it return:
//------------------------------------------
NAME math english chinese //DataField
myname 210 210 210

[welyngj]

我写了个，但是出现问题
declare
v_sql varchar(2000):='select name';
begin
select v_sql||',sum(case subject when '''||subject||'''then score end )'||subject into v_sql from (select distinct subject from test) a;
v_sql:=v_sql||'from test group by name';
execute immediate( v_sql);
end;
/

*
ERROR 位于第 1 行:
ORA-01422: 实际返回的行数超出请求的行数
ORA-06512: 在line 4
怎么办？
在sqlserver中这种问题太容易实现了。
oracle的语法不熟

[beckhambobo]

select name,sum(decode(SUBJECT,'math',SCORE)) math,
sum(decode(subject,'english',score)) english,
sum(decode(subject,'chinese',score)) chinese
from tabname group by name

[LGQDUCKY]

declare
v_sql varchar(2000):='select name';
begin
select v_sql||',sum(case subject when '''||subject||'''then score end )'||subject into v_sql from (select distinct subject from test) a;
v_sql:=v_sql||'from test group by name';
--execute immediate( v_sql);
open c for v_sql;
end;
/

这个只能返回单行，对于多行只能是使用游标。