oracle 交叉表

在oracle的test表中插入记录
INSERTINTOtestvalues(N'张三',N'语文',60)

INSERTINTOtestvalues(N'李四',N'数学',70)

INSERTINTOtestvalues(N'王五',N'英语',80)

INSERTINTOtestvalues(N'王五',N'数学',75)

INSERTINTOtestvalues(N'王五',N'语文',57)

INSERTINTOtestvalues(N'李四',N'语文',80)

INSERTINTOtestvalues(N'张三',N'英语',100)
交叉表语句的实现：

--用于：交叉表的列数是确定的

selectname,sum(casesubjectwhen'数学'thensourceelse0end)as'数学',

sum(casesubjectwhen'英语'thensourceelse0end)as'英语',

sum(casesubjectwhen'语文'thensourceelse0end)as'语文'

fromtest

groupbyname
用oracle存储过程实现
CREATEORREPLACEPACKAGEBODY"TEST1"as

proceduresp_test(p_cursoroutt_cursor)

is
sqlstrvarchar2(32767);
begin
sqlstr:='selectname,';
--构造字符串
forv_curin(selectdistinctsubjectfromtest)
loop
sqlstr:=sqlstr||'sum(casesubjectwhen'''||v_cur.subject||'''thenscoreelse0end)as'||v_cur.subject||',';
endloop;



sqlstr:=substr(sqlstr,0,length(sqlstr)-1)||'fromtestgroupbyname';

openp_cursorforsqlstr;

end;
end;