Oracle行列转换汇总

最新推荐文章于 2025-05-23 21:04:26 发布

sinat_28472983

最新推荐文章于 2025-05-23 21:04:26 发布

阅读量6k

点赞数 2

CC 4.0 BY-SA版权

分类专栏： SQL

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

SQL 专栏收录该内容

11 篇文章

订阅专栏

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

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

2. 列转行：unpivot

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

2.0 创建测试表格

create table test0 (plandate varchar2(20), inqty number, outqty number); 
 
insert into  test0 values ('20180911', 10, 20);
insert into  test0 values ('20180912', 4, 8);
insert into  test0 values ('20180913', 3, 6);

2.1 unpivot 列转行

SELECT   PLANDATE 
        ,PLANTYPE
        ,PLANQTY 
    FROM TEST0 
UNPIVOT (PLANQTY FOR PLANTYPE IN (INQTY, OUTQTY))

→

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

3. 多行转字符串：wm_concat 或 row_number + lead

3.0 创建test表

create table test(id number,name varchar2(20));  
      
insert into test values(1,'a');  
insert into test values(1,'b');  
insert into test values(1,'c');  
insert into test values(2,'d');  
insert into test values(2,'e');

3.1 wm_concat

a. 行转列，默认逗号隔开；并按ID分组合并name

SELECT ID
      ,TO_CHAR(WM_CONCAT(NAME)) NAME 
  FROM TEST 
 GROUP BY ID;

→

b. 把结果里的逗号替换成"|"

SELECT ID
      ,TO_CHAR (REPLACE (WM_CONCAT (NAME),',','|'))
  FROM TEST
 GROUP BY ID;

→

c. 扩展：用于快速复制输入所有栏位名

SELECT 
     'CREATE OR REPLACE VIEW AS SELECT '|| TO_CHAR (WM_CONCAT(COLUMN_NAME)) || ' FROM TEST' SQLSTR   
 FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME LIKE UPPER ('%test1%');

→

3.2 listagg

SELECT ID
      ,LISTAGG(NAME,',')  WITHIN GROUP (ORDER BY NAME) AS NAME_CHAR
FROM TEST
GROUP BY ID;

3.3 row_number + lead

SELECT ID, NAME_CHAR
  FROM (SELECT ID,
               ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NAME) AS RN,
               NAME || LEAD(',' || NAME, 1) OVER(PARTITION BY ID ORDER BY NAME) ||
                       LEAD(',' || NAME, 2) OVER(PARTITION BY ID ORDER BY NAME) ||
                       LEAD(',' || NAME, 3) OVER(PARTITION BY ID ORDER BY NAME) AS NAME_CHAR
          FROM TEST)
WHERE RN = 1
ORDER BY 1;

→ →

3.3 sys_connect_by_path

SELECT  T.ID
       ,SUBSTR(SYS_CONNECT_BY_PATH(T.NAME, ','), 2) STR
  FROM (
        SELECT ID
              ,NAME
              ,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NAME) RN
        FROM TEST
        ) T
WHERE CONNECT_BY_ISLEAF = 1
START WITH RN = 1
CONNECT BY RN = PRIOR RN + 1 AND ID = PRIOR ID;

参考自：http://www.itpub.net/thread-1017026-1-1.html

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

4.多列转字符串：concat 或 ||

4.1 concat 或 ||

SELECT ID, NAME, CONCAT(ID,NAME) STR FROM TEST;
  
SELECT ID, NAME, ID||NAME STR FROM TEST;

→

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

5.字符串转多列：regexp_substr 或 substr + instr，拆分字符串

5.0 创建test1表

create table test1 (id number, name varchar2 (20));  
      
insert into test1  values (1, 'a,b,c');   
insert into test1  values (2, 'd,e');

5.1 regexp_substr

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)

__srcstr ：需要进行正则处理的字符串

__pattern ：进行匹配的正则表达式

__position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）

__occurrence ：标识第几个匹配组，默认为1

__modifier ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

SELECT ID
      ,NAME
      ,RTRIM(REGEXP_SUBSTR(NAME || ',', '.*?' || ',', 1, 1), ',') N1
      ,RTRIM(REGEXP_SUBSTR(NAME || ',', '.*?' || ',', 1, 2), ',') N2
      ,RTRIM(REGEXP_SUBSTR(NAME || ',', '.*?' || ',', 1, 3), ',') N3
  FROM TEST1
ORDER BY 1;

→

5.2 substr + instr

SELECT ID
      ,NAME
      ,SUBSTR(NAME, 1, INSTR(NAME || ',', ',', 1, 1) - 1) N1
      ,SUBSTR(NAME,
              INSTR(NAME || ',', ',', 1, 1) + 1,
              INSTR(NAME || ',', ',', 1, 2) - INSTR(NAME || ',', ',', 1, 1) - 1) N2
      ,SUBSTR(NAME,
              INSTR(NAME || ',', ',', 1, 2) + 1,
              INSTR(NAME || ',', ',', 1, 3) - INSTR(NAME || ',', ',', 1, 2) - 1) N3
  FROM TEST1
ORDER BY 1;

数据效果同上

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

6.字符串转多行

6.1 SEQUENCE series
这类方法主要是要产生一个连续的整数列，产生连续整数列的方法有很多，主要有：CONNECT BY,ROWNUM+all_objects,CUBE等。

--LEVEL产生连续整数列
SELECT T.ID,
       C.LEVEL_NUM,
       SUBSTR(T.NAME1,
              INSTR(T.NAME1, ',', 1, C.LEVEL_NUM) + 1,
              INSTR(T.NAME1, ',', 1, C.LEVEL_NUM + 1) -
              (INSTR(T.NAME1, ',', 1, C.LEVEL_NUM) + 1)) NAME
  FROM (SELECT ID,
               ',' || NAME || ',' AS NAME1,
               LENGTH(NAME || ',') - NVL(LENGTH(REPLACE(NAME, ',')), 0) AS CNT
          FROM TEST1) T,
       (SELECT LEVEL LEVEL_NUM FROM DUAL CONNECT BY LEVEL <= 5) C
WHERE C.LEVEL_NUM <= T.CNT
ORDER BY 1, 2;

--ROWNUM产生连续整数列
SELECT T.ID,
       C.RN,
       SUBSTR(T.NAME1,
              INSTR(T.NAME1, ',', 1, C.RN) + 1,
              INSTR(T.NAME1, ',', 1, C.RN + 1) -
              (INSTR(T.NAME1, ',', 1, C.RN) + 1)) NAME
  FROM (SELECT ID,
               ',' || NAME || ',' AS NAME1,
               LENGTH(NAME || ',') - NVL(LENGTH(REPLACE(NAME, ',')), 0) AS CNT
          FROM TEST1) T,
       (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 5) C
WHERE C.RN <= T.CNT
ORDER BY 1, 2;

→

6.2 Hierarchical + DBMS_RANDOM

trim([leading/trailing/both][匹配字符串或数值][from][需要被处理的字符串或数值])

SELECT ID,
       LEVEL AS LEVEL_NUM,
       RTRIM (REGEXP_SUBSTR(NAME || ',', '.*?' || ',', 1, LEVEL), ',') AS NAME
  FROM TEST1
CONNECT BY ID = PRIOR ID
       AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
       AND LEVEL <= LENGTH (REGEXP_REPLACE (NAME || ',', '[^' || ',' || ']', NULL))
ORDER BY 1, 2;

数据效果同上，实际应用效果：

--快速复制表中所有栏位名
SELECT TO_CHAR (WM_CONCAT(COLUMN_NAME)) SQLSTR   
  FROM USER_TAB_COLUMNS 
 WHERE TABLE_NAME LIKE UPPER ('%MOD_WIPGROUP%');

--将按逗号分隔的工序字符串拆成行
SELECT FACTORYNAME,FLOWGROUP,SEQ,WIPGROUPNAME,MOVEMENTOPERATION,
     --LEVEL AS LEVEL_NUM,
       RTRIM (REGEXP_SUBSTR(WIPOPERATIONLIST || ',', '.*?' || ',', 1, LEVEL), ',') AS WIPOPERATIONLIST
  FROM MOD_WIPGROUP
CONNECT BY FACTORYNAME       = PRIOR FACTORYNAME
       AND FLOWGROUP         = PRIOR FLOWGROUP
       AND SEQ               = PRIOR SEQ
       AND WIPGROUPNAME      = PRIOR WIPGROUPNAME
       AND MOVEMENTOPERATION = PRIOR MOVEMENTOPERATION
       AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
       AND LEVEL <= LENGTH (REGEXP_REPLACE (WIPOPERATIONLIST || ',', '[^' || ',' || ']', NULL))
ORDER BY 1, 2;

6.3 Hierarchical + CONNECT_BY_ROOT

with t as
(select 'i;am;a;test;mine' as str from dual)
select level,
str,
regexp_substr(t.str, '[^;]+', 1, level) str_single
from t
connect by level <= length(t.str) - length(replace(t.str, ';', '')) + 1;

SELECT id,
REGEXP_SUBSTR(name,'^[^,]*',1,1) first_one,
REGEXP_SUBSTR(name,'^[^,]*',1,3) lll,
REGEXP_SUBSTR(name, '([[:alpha:]]+)$') last_one
FROM test1
ORDER BY 1;