Oracle 行记录转列记录，动态列字段查询实例

------------第一步：创建测试表
CREATE TABLE TEST_1
(
  WL VARCHAR2(10),
  XYSL INTEGER,
  XYCK VARCHAR2(10),
  XCLCK VARCHAR2(10),
  XCLCKSL INTEGER,
  PC INTEGER
);

------------第二步：插入测试数据
INSERT INTO TEST_1 VALUES ('A1', 2, 'C1', 'C1', 20, 123);
INSERT INTO TEST_1 VALUES ('A1', 2, 'C1', 'C2', 30, 111);
INSERT INTO TEST_1 VALUES ('A1', 2, 'C1', 'C2', 20, 222);
INSERT INTO TEST_1 VALUES ('A1', 2, 'C1', 'C3', 10, 211);
INSERT INTO TEST_1 VALUES ('A2', 3, 'C4', 'C1', 40, 321);
INSERT INTO TEST_1 VALUES ('A2', 3, 'C4', 'C4', 50, 222);
INSERT INTO TEST_1 VALUES ('A2', 3, 'C4', 'C4', 60, 333);
INSERT INTO TEST_1 VALUES ('A2', 3, 'C4', 'C5', 70, 223);
COMMIT;


------------第三步：动态生成结果表的语句块
DECLARE
  V_SQL VARCHAR2(2000);
  CURSOR CURSOR_1 IS
    SELECT DISTINCT T.XCLCK
      FROM TEST_1 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_1 GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';
    --DBMS_OUTPUT.PUT_LINE(V_SQL);
    V_SQL := 'CREATE TABLE RESULT_1  AS '||  V_SQL;
    EXECUTE IMMEDIATE V_SQL;
END;



------------第四步：测试

--------------- 查询结果
SELECT * FROM RESULT_1 T;
WL                        XYSL XYCK               C1         C2         C3         C4         C5
---------- ------------------- ---------- ---------- ---------- ---------- ---------- ----------
A1                           2 C1                 20         50         10          0          0
A2                           3 C4                 40          0          0        110         70
--------------- 删除结果表
DROP TABLE RESULT_1; 
 
 
------------ 再次插入测试数据
INSERT INTO TEST_1 VALUES('A1', 2, 'C1', 'C6' ,        20,         124); 
INSERT INTO TEST_1 VALUES('A2', 2, 'C1', 'C7' ,        30,         121); 
INSERT INTO TEST_1 VALUES('A3', 2, 'C1', 'C8' ,        20,         322); 
COMMIT;

---------  重新运行 动态生成结果表 语句块（执行第二步）

--------------- 查询结果，发现列有变化
SELECT * FROM RESULT_1 T;
WL     XYSL XYCK          C1       C2         C3         C4         C5         C6         C7         C8
----- ----- -------- ------- -------- ---------- ---------- ---------- ---------- ---------- ----------
A1        2 C1            20       50         10          0          0         20          0          0
A2        2 C1             0        0          0          0          0          0         30          0
A2        3 C4            40        0          0        110         70          0          0          0
A3        2 C1             0        0          0          0          0          0          0         20