Oracle行列转换汇总

最新推荐文章于 2024-08-27 16:18:06 发布

sinat_28472983

最新推荐文章于 2024-08-27 16:18:06 发布

阅读量5.9k

点赞数 2

分类专栏： SQL

本文链接：https://blog.csdn.net/sinat_28472983/article/details/82631982

版权

本文详细介绍了Oracle中如何进行行列转换，包括使用pivot、case when、decode进行行转列，unpivot进行列转行，wm_concat和row_number+lead实现多行转字符串，concat和||进行多列转字符串，以及regexp_substr和substr+instr将字符串拆分为多列。同时，还提到了字符串转多行的各种方法，如SEQUENCE系列、Hierarchical查询结合DBMS_RANDOM和CONNECT_BY_ROOT。

摘要由CSDN通过智能技术生成

1.行转列：pivot 、case when 或 decode

**informatica normalizer transformation也可以实现行列转换**

pivot（聚合函数 for 列名 in（类型））: pivot ( sum ( planqty ) for plantype in ( 'in', 'out' ) ) --有聚合函数

unpivot.：unpivot (planqty for plantype in (inqty, outqty))

1.0 创建测试表格

CREATE TABLE XXXMGR.FAB_BSFACTORYDAILYPLAN
(
  FACTORYNAME      VARCHAR2 (40 BYTE),
  PRODUCTSPECNAME  VARCHAR2 (40 BYTE),
  PRODUCTTYPE      VARCHAR2 (40 BYTE),
  PLANDATE         VARCHAR2 (40 BYTE),
  PLANTYPE         VARCHAR2 (40 BYTE),
  PLANQTY          VARCHAR2 (40 BYTE),
  LASTEVENTUSER    VARCHAR2 (40 BYTE),
  LASTEVENTTIME    DATE,
  LASTEVENTNAME    VARCHAR2 (40 BYTE)
)
TABLESPACE ODS_XXX_DAT;

CREATE UNIQUE INDEX XXXMGR.BSFACTORYDAILYPLAN_PK ON XXXMGR.FAB_BSFACTORYDAILYPLAN
(FACTORYNAME, PRODUCTSPECNAME, PRODUCTTYPE, PLANDATE, PLANTYPE)
TABLESPACE ODS_XXX_IDX;

ALTER TABLE XXXMGR.FAB_BSFACTORYDAILYPLAN ADD (
  CONSTRAINT BSFACTORYDAILYPLAN_PK
  PRIMARY KEY
  (FACTORYNAME, PRODUCTSPECNAME, PRODUCTTYPE, PLANDATE, PLANTYPE)
  USING INDEX XXXMGR.BSFACTORYDAILYPLAN_PK
  ENABLE VALIDATE);

1.1 行转列：pivot pivot ( sum ( planqty ) for plantype in ( 'in', 'out' ) ) --in、out数量转为列

SELECT * FROM
 (SELECT FACTORYNAME
        ,PRODUCTSPECNAME
        ,PRODUCTTYPE
        ,PLANDATE
        ,PLANTYPE
        ,PLANQTY
    FROM FAB_BSFACTORYDAILYPLAN)
PIVOT (SUM (PLANQTY) FOR PLANTYPE IN ('IN' INQTY, 'OUT' OUTQTY))

1.2 行转列：case when 或 decode （更繁琐）

--CASE WHEN 
SELECT FACTORYNAME
      ,PRODUCTSPECNAME
      ,PRODUCTTYPE
      ,PLANDATE
      ,CASE WHEN PLANTYPE = 'IN' THEN PLANQTY END INQTY
      ,CASE WHEN PLANTYPE = 'OUT' THEN PLANQTY END OUTQTY
  FROM FAB_BSFACTORYDAILYPLAN

--DECODE
SELECT FACTORYNAME
      ,PRODUCTSPECNAME
      ,PRODUCTTYPE
      ,PLANDATE
      ,DECODE (PLANTYPE,'IN',PLANQTY) INQTY
      ,DECODE (PLANTYPE,'OUT',PLANQTY) OUTQTY
      --Rule5816:Use CASE construct rather than DECODE
  FROM FAB_BSFACTORYDAILYPLAN

******************************************************************************************************************************************************