环球微资讯！oracle行转列是什么？动态行转不定列

(资料图片)

原文链接

/*物料 需要数量 需要仓库 现存量仓库 现存量仓库数量 批次  A1 2 C1 C1         20         123  A1 2 C1 C2         30         111  A1 2 C1 C2         20         222  A1 2 C1 C3         10         211  A2 3 C4 C1         40         321  A2 3 C4 C4         50         222  A2 3 C4 C4         60         333  A2 3 C4 C5         70         223  我需要把上面的查询结果转换为下面的。 物料 需要数量 需要仓库 C1 C2 C3 C4 C5  A1 2 C1 20 50 10 0 0  A2 3 C4 40 0 0 110 70  */  ---------------------------------------------------------------建表 ----------------判断表是否存在 declare num number;  begin      select count(1) into num from user_tables where table_name="TEST";     if num>0 then        execute immediate "drop table TEST";     end if; end; ----------------建表 CREATE TABLE TEST(     WL VARCHAR2(10),     XYSL INTEGER,     XYCK VARCHAR2(10),     XCLCK VARCHAR2(10),     XCLCKSL INTEGER,     PC INTEGER ); ----------------第一部分测试数据 INSERT INTO TEST VALUES("A1", 2, "C1", "C1" ,        20,         123);  INSERT INTO TEST VALUES("A1", 2, "C1", "C2" ,        30,         111);  INSERT INTO TEST VALUES("A1", 2, "C1", "C2" ,        20,         222);  INSERT INTO TEST VALUES("A1", 2, "C1", "C3" ,        10,         211);  INSERT INTO TEST VALUES("A2", 3, "C4", "C1" ,        40,         321);  INSERT INTO TEST VALUES("A2", 3, "C4", "C4" ,        50,         222);  INSERT INTO TEST VALUES("A2", 3, "C4", "C4" ,        60,         333);  INSERT INTO TEST VALUES("A2", 3, "C4", "C5" ,        70,         223); COMMIT; --select * from test; ---------------------------------------------------------------行转列的存储过程 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; ----------------结果 SELECT * FROM RESULT 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

----------------第二部分测试数据 INSERT INTO TEST VALUES("A1", 2, "C1", "C6" ,        20,         124);  INSERT INTO TEST VALUES("A2", 2, "C1", "C7" ,        30,         121);  INSERT INTO TEST VALUES("A3", 2, "C1", "C8" ,        20,         322);  COMMIT; ----------------报告存储过程，生成视图 BEGIN   P_TEST;                END; ----------------结果 SELECT * FROM RESULT 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 --------------- 删除实体 DROP VIEW RESULT; DROP PROCEDURE P_TEST; DROP TABLE TEST;