动态行转列

在开发过程中，很多时候并不知道有多上行转换为列，所以只有通过动态来进行转换。下面是我在工作中的一个简答例子（其实行转列的动态sql无非就是拼sql语句，然后通过游标的方式进行读取转换）：

CREATE OR REPLACE PROCEDURE P_TEST_LIUHC(CYCLE_DATE IN VARCHAR2)
AUTHID CURRENT_USER
IS
 V_SQL VARCHAR2(20000);
-- P_YW_ASSESS_ID  NUMBER;
-- P_ASSESS_COLUMN VARCHAR2(30);

 ----------查找所使用到的指标ID
 CURSOR CURSOR_1 IS
       SELECT DISTINCT YW_ASSESS_ID,ASSESS_COLUMN
       FROM   RPT_KPI_YW_QD X, CS_ASSESS Y
       WHERE  X.YW_ASSESS_ID=Y.ASSESS_ID AND
              STATEMENT_DATE = ''||CYCLE_DATE||'' AND
              YW_ASSESS_ID IS NOT NULL  AND YW_KPI_TYPE<>'计件考核宽表'
       ORDER  BY YW_ASSESS_ID,ASSESS_COLUMN;

BEGIN
  ---定义一个SQL前缀(以下采用拼凑SQL的方式逐级拼凑)
  V_SQL := 'SELECT     YW_KPI_TYPE,
                       YW_BSS_ORG_ID,
                       YW_BSS_ORG_NAME,
                       YW_CHANNEL_TYPE_ID';

-------开始循使用  （获取所有指标并分组）
 --OPEN CURSOR_1;
   --FETCH CURSOR_1 INTO P_YW_ASSESS_ID,P_ASSESS_COLUMN;
    FOR V_XCLCK IN CURSOR_1
      LOOP
        V_SQL := V_SQL || ',' || 'SUM(DECODE(YW_ASSESS_ID,''' || V_XCLCK.YW_ASSESS_ID ||
                 ''',AMOUNT,0)) AS ' || V_XCLCK.ASSESS_COLUMN;
      END LOOP;

      V_SQL := V_SQL || ' ,STATEMENT_DATE,REMARK '||' FROM RPT_KPI_YW_QD WHERE STATEMENT_DATE = '||CYCLE_DATE||'
                                             GROUP BY YW_KPI_TYPE,
                                                      YW_BSS_ORG_ID,
                                                      YW_BSS_ORG_NAME,
                                                      YW_CHANNEL_TYPE_ID,
                                                      STATEMENT_DATE,
                                                      REMARK';
     -- DBMS_OUTPUT.PUT_LINE(V_SQL);
     -- EXECUTE IMMEDIATE 'DROP VIEW RESULT_LIUHC';
      DROP_TEMP_TABLE('RESULT_LIUHC_1');
      V_SQL := 'CREATE TABLE RESULT_LIUHC_1  AS '||  V_SQL;
      --DBMS_OUTPUT.PUT_LINE(V_SQL);
      EXECUTE IMMEDIATE V_SQL;

COMMIT;

END P_TEST_LIUHC;

该过程主要通过账期来对表RPT_KPI_YW_QD进行行转列。希望对其他人有所启发。