Oracle 行轉列 的例子

一個同事寫的，那出來和大家分享一下

 

CREATE TABLE SCORE
(
  FNAME NVARCHAR2(50),
  FSUBJECT NVARCHAR2(50),
  FRESULT DECIMAL(18,0)
);
insert into score values('張三','語文',20);
insert into score values('張三','數學',20);
insert into score values('張三','英語',20);
insert into score values('張三','地理',20);

insert into score values('張三','語文',1);
insert into score values('張三','數學',2);
insert into score values('張三','英語',3);
insert into score values('張三','地理',4);

insert into score values('李四','語文',20);
insert into score values('李四','數學',20);
insert into score values('李四','英語',20);
insert into score values('李四','化學',20);
insert into score values('李四','電腦技術',20);

BEGIN
DECLARE V_SQL NVARCHAR2(8000);
BEGIN
SELECT 'SELECT  FNAME 姓名' INTO V_SQL FROM DUAL;

SELECT V_SQL||','||LTRIM(MAX(SYS_CONNECT_BY_PATH(X, ',')), ',') INTO V_SQL
FROM (
      SELECT * FROM (
        SELECT RN,'
        SUM(CASE TO_CHAR(FSUBJECT) WHEN '''||FSUBJECT||''' THEN FRESULT ELSE 0 END) '||FSUBJECT  X
        FROM (SELECT ROWNUM RN,FSUBJECT FROM (SELECT FSUBJECT FROM SCORE GROUP BY FSUBJECT)) I

          ) ORDER BY RN
    )Y
START WITH RN = (SELECT COUNT(1) FROM (SELECT FSUBJECT FROM SCORE GROUP BY FSUBJECT) )
CONNECT BY  PRIOR  RN-1 = RN;

SELECT V_SQL || 'FROM SCORE GROUP BY FNAME'  INTO V_SQL FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_SQL);
END;
END;