Ordinary hut

人间一福地,胜似天仙宫
posts - 61, comments - 50, trackbacks - 0, articles - 1

oracle行列转换

Posted on 2010-01-07 11:08 landor 阅读(517) 评论(0)  编辑  收藏 所属分类: oracle
oracle 10g之后,可以用wm_concat函数来做
但是这个是把多行汇总到一个字段中,如果是多个动态字段,可以根据这个帖子来做

以下转自http://blog.163.com/benthos@126/blog/static/127943815200910189508556/

/*物料 需要数量 需要仓库 现存量仓库 现存量仓库数量 批次

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;  

0

0

0





只有注册用户登录后才能发表评论。


网站导航: