表a有字段'学生id','科目'，'分数'，怎么查出结果集如：'学生id','科目1分数','科目2分数'..'科目n分数'

Question

交叉表阿 。自己做个空间阿

Answer 1

上周末写出来了 ，类似像skystar99047(天星)写的那样，不过如果数据量大的话相信是很慢的，所以我把它写到view里面去了。
谢谢大家
CREATE OR REPLACE VIEW AGENTEXAM_MARK_ENTERFOR ( ID, 
NAME, SEX, BIRTHDAY, NATION, 
IDCARD, STUDYLEVEL, SPECIALTY, FOREIGNLANGUAGE, 
ADDRESS, WORKUNIT, UNITTYPE, AREA, 
TEL, LEARNINGRESUME, WORKRESUME, AUDITOPINION, 
EXAMCARDNO, PATENTAGENTEXAMROOMID, ENTERFEE, SORTNO, 
REMARK, ORGANTYPEID, CREATEUSERID, CREATEDATE, 
PATENTAGENTEXAMREQUESTID, PHOTOPATH, STATUS, PHONE, 
NOWADDRESS, EXAM0001_KSKSKM, EXAM0002_KSKSKM, EXAM0003_KSKSKM, 
EXAM0004_KSKSKM ) AS SELECT a.*,b.EXAM0001_KSKSKM,b.EXAM0002_KSKSKM,b.EXAM0003_KSKSKM,b.EXAM0004_KSKSKM 
FROM PATENTAGENTEXAM_ENTERFOR a, ( 
SELECT PATENTAGENTEXAMENTERFORID,SUM(A1) EXAM0001_KSKSKM,SUM(A2) EXAM0002_KSKSKM,SUM(A3) EXAM0003_KSKSKM,SUM(A4) EXAM0004_KSKSKM, 
+SUM(A1)+SUM(A2)+SUM(A3)+SUM(A4) SUMMARK FROM  
(SELECT PATENTAGENTEXAMENTERFORID, 
(DECODE(EXAMITEM,'0001_KSKSKM',SUM(PAPERMARK))) A1, 
(DECODE(EXAMITEM,'0002_KSKSKM',SUM(PAPERMARK))) A2, 
(DECODE(EXAMITEM,'0003_KSKSKM',SUM(PAPERMARK))) A3, 
(DECODE(EXAMITEM,'0004_KSKSKM',SUM(PAPERMARK))) A4 
FROM PATENTAGENTEXAM_MARK GROUP BY PATENTAGENTEXAMENTERFORID,EXAMITEM) 
GROUP BY PATENTAGENTEXAMENTERFORID) b WHERE a.id=b.PATENTAGENTEXAMENTERFORID