oracle行转列（动态行转不定列)

最新推荐文章于 2023-01-05 08:36:51 发布

weixin_33797791

最新推荐文章于 2023-01-05 08:36:51 发布

阅读量1.5k

点赞数

文章标签：数据库 python

原文链接：https://my.oschina.net/youfen/blog/1863241

版权

2019独角兽企业重金招聘Python工程师标准>>>

---------------------------------------------------------------行转列的存储过程
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;