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;