Question

ORACLE行转列问题

新手求教oracle中此类行转列的问题，如何讲左表的显示内容转换为右表，求高手解答，万分感谢，假设sql为select type as 类别, value as 值,date as 日期 from test ;

Answer 1

ORACLE行转列问题
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;