同一个交叉表的动态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;
&