Oracle 动态转列，事例表和数据

 

接上一篇，Oracle 动态转列，事例表和数据，请看上一篇

DECLARE
  V_STRING VARCHAR(32767);
BEGIN

  --生成视图
  SELECT 'CREATE OR REPLACE VIEW V_TEST_1 AS SELECT T1.*,'||
         TO_CHAR(WM_CONCAT ('DECODE(COL2, '||COL1||',COL3) AS '||COL1))||
        ' FROM (select distinct col2,col3 from TEST_1) T1,(SELECT '||
         TO_CHAR(WM_CONCAT (''''||COL2||''''||' AS '||COL1))||
         ' FROM DUAL) T' /*INTO V_STRING*/ FROM TEST_1;

  EXECUTE IMMEDIATE V_STRING;
  --生成实体表
  BEGIN EXECUTE IMMEDIATE 'DROP TABLE TEST_2'; EXCEPTION WHEN OTHERS THEN NULL; END;

  SELECT 'CREATE TABLE TEST_2 AS SELECT T1.*,'||
         TO_CHAR(WM_CONCAT ('DECODE(COL2, '||COL1||',COL3) AS '||COL1))||
        ' FROM TEST_1 T1,(SELECT '||
         TO_CHAR(WM_CONCAT (''''||COL2||''''||' AS '||COL1))||
         ' FROM DUAL) T' INTO V_STRING FROM TEST_1;
  EXECUTE IMMEDIATE V_STRING;

END;