ͬһ¸ö½»²æ±íµÄ¶¯Ì¬SQLʵÏÖ

DECLARE
 TYPE list IS TABLE OF t_stu.project%TYPE INDEX BY BINARY_INTEGER;
 project_list list;
 v_pro_num NUMBER;
 sql_str VARCHAR(255);
 
t_stu
SID SUBJECT SCORE
1        Êýѧ         99
1        Ó¢Óï         68
1        ·¨ÂÉ         79
2        Êýѧ         92
2        Ó¢Óï         72
2        ·¨ÂÉ         96
ʵÏÖ======
SID
Êýѧ
Ó¢Óï
·¨ÂÉ
1
99
68
79
2
92
72
96
 
DECLARE
 TYPE list IS TABLE OF t_stu.subject%TYPE INDEX BY BINARY_INTEGER;
 subject_list list;
 v_pro_num NUMBER;
 sql_str VARCHAR(255);
BEGIN
 SELECT COUNT(DISTINCT subject) INTO v_pro_num from t_stu;
 sql_str := 'SELECT id ';
 
 FOR v_idx in 1..v_pro_num LOOP
 
  IF v_idx = 1 THEN
  
   SELECT DISTINCT subject INTO subject_list(v_idx) from t_stu WHERE rownum = 1;
   
  ELSE
  
   SELECT subjectINTO subject_list(v_idx)
   from (SELECT DISTINCT subject, rownum row_num from t_stu WHERE rownum < v_idx + 1)
   WHERE row_num = v_idx;
   
  END IF;
  
  sql_str := sql_str || ', SUM(DECODE(subject,''' || subject_list(v_idx) || ''',score,0)) AS ' || subject_list(v_idx);
  
 END LOOP;
 
 sql_str := sql_str || ' from t_stu GROUP BY id';
 DBMS_OUTPUT.PUT_LINE('sql--->' || sql_str);
  
 EXEC IMMEDIATE sql_str;
&