oracle不定列,oracle行转列（动态行转不定列)

2019独角兽企业重金招聘Python工程师标准>>>

---------------------------------------------------------------行转列的存储过程

CREATE OR REPLACE PROCEDURE P_TEST IS

V_SQL VARCHAR2(2000);

CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM TEST T ORDER BY XCLCK;

BEGIN

V_SQL := 'SELECT WL,XYSL,XYCK';

FOR V_XCLCK IN CURSOR_1

LOOP

V_SQL := V_SQL || ',' || 'SUM(DECODE(XCLCK,''' || V_XCLCK.XCLCK ||

''',XCLCKSL,0)) AS ' || V_XCLCK.XCLCK;

END LOOP;

V_SQL := V_SQL || ' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';

--DBMS_OUTPUT.PUT_LINE(V_SQL);

V_SQL := 'CREATE OR REPLACE VIEW RESULT  AS '||  V_SQL;

--DBMS_OUTPUT.PUT_LINE(V_SQL);

EXECUTE IMMEDIATE V_SQL;

END;

----------------------------------------------------------------结果

----------------执行存储过程，生成视图

BEGIN

P_TEST;

END;