2022-01-05

Oracle-Row-Column-Conversion-1

Oracle行列转换总汇

转载：https://blog.csdn.net/sinat_28472983/article/details/82631982

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))

创建 RC_CON_1 测试表格

CREATE TABLE RC_CON_1
(
  PLANDATE         VARCHAR2 (40 BYTE),
  PLANTYPE         VARCHAR2 (40 BYTE),
  PLANQTY          VARCHAR2 (40 BYTE)
);
INSERT INTO RC_CON_1 values ('2021-11-22','IN', 1);
INSERT INTO RC_CON_1 values ('2021-12-22','OUT',100);
COMMIT;

1.1 行转列：pivot

pivot ( sum ( planqty ) for plantype in ( ‘in’, ‘out’ ) ) –in、out数量转为列

SELECT * FROM
 (SELECT PLANDATE
        ,PLANTYPE
        ,PLANQTY
    FROM RC_CON_1)
PIVOT (SUM (PLANQTY) FOR PLANTYPE IN ('IN' INQTY, 'OUT' OUTQTY));

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

SELECT PLANDATE
      ,CASE WHEN PLANTYPE = 'IN' THEN PLANQTY END INQTY
      ,CASE WHEN PLANTYPE = 'OUT' THEN PLANQTY END OUTQTY
  FROM RC_CON_1

SELECT PLANDATE
      ,DECODE (PLANTYPE,'IN',PLANQTY) INQTY
      ,DECODE (PLANTYPE,'OUT',PLANQTY) OUTQTY
  FROM RC_CON_1

2. 列转行：unpivot

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

创建 RC_CON_2 测试表格

CREATE TABLE RC_CON_2 (
plandate varchar2(20), 
inqty number, 
outqty number
); 
INSERT INTO  RC_CON_2 values ('20180911', 10, 20);
INSERT INTO  RC_CON_2 values ('20180912', 4, 8);
INSERT INTO  RC_CON_2 values ('20180913', 3, 6);
COMMIT;

2.1 unpivot 列转行

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

3. 多行转字符串

listagg 或 row_number + lead

创建 RC_CON_3 测试表

CREATE TABLE RC_CON_3(
    id number,
    name varchar2(20)
); 
insert into RC_CON_3 values(1,'a');  
insert into RC_CON_3 values(1,'b');  
insert into RC_CON_3 values(1,'c');  
insert into RC_CON_3 values(2,'d');  
insert into RC_CON_3 values(2,'e'); 
COMMIT;

3.1 listagg

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

SELECT ID
      ,LISTAGG(NAME) NAME 
  FROM RC_CON_3 
 GROUP BY ID;

注：11gr2和12C上已经摒弃了wm_concat 函数，该方法替换成了 listagg 函数

b. 把结果里使用分号 “|” 隔开

SELECT ID
     ,LISTAGG(NAME,'|') NAME 
 FROM RC_CON_3 
GROUP BY ID;

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

SELECT 
     'CREATE OR REPLACE VIEW AS SELECT '|| TO_CHAR (LISTAGG(COLUMN_NAME,',')) || ' FROM RC_CON_3' SQLSTR   
 FROM USER_TAB_COLUMNS 
WHERE TABLE_NAME LIKE UPPER ('%RC_CON_3%');

3.2 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 RC_CON_3)
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 RC_CON_3
        ) T
WHERE CONNECT_BY_ISLEAF = 1
START WITH RN = 1
CONNECT BY RN = PRIOR RN + 1 AND ID = PRIOR ID;

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

创建 RC_CON_4 测试表

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

4.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 RC_CON_4
ORDER BY 1;

4.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 RC_CON_4
ORDER BY 1;

5.字符串转多行

5.1 SEQUENCE series

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

以下案例操作使用 RC_CON_4 测试表

–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 RC_CON_4) 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 RC_CON_4) T,
       (SELECT ROWNUM RN FROM DUAL CONNECT BY ROWNUM <= 5) C
WHERE C.RN <= T.CNT
ORDER BY 1, 2;

5.2 Hierarchical + DBMS_RANDOM

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

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

5.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 RC_CON_4
ORDER BY 1;

其他实现

1. 案例

1	将多行数据转换成指定列数，多出来的数据自动换到下一行

测试数据

CREATE TABLE RC_E_1
(
  ID         VARCHAR2 (40 BYTE)
);
INSERT INTO RC_E_1 (ID) VALUES ('01301');
INSERT INTO RC_E_1 (ID) VALUES ('09156');
INSERT INTO RC_E_1 (ID) VALUES ('04155');
INSERT INTO RC_E_1 (ID) VALUES ('04158');
INSERT INTO RC_E_1 (ID) VALUES ('08112');
INSERT INTO RC_E_1 (ID) VALUES ('05102');
INSERT INTO RC_E_1 (ID) VALUES ('08153');
INSERT INTO RC_E_1 (ID) VALUES ('03156');
INSERT INTO RC_E_1 (ID) VALUES ('09112');
INSERT INTO RC_E_1 (ID) VALUES ('09108');
INSERT INTO RC_E_1 (ID) VALUES ('08159');
INSERT INTO RC_E_1 (ID) VALUES ('15151');
INSERT INTO RC_E_1 (ID) VALUES ('08158');
INSERT INTO RC_E_1 (ID) VALUES ('08110');
INSERT INTO RC_E_1 (ID) VALUES ('03159');
INSERT INTO RC_E_1 (ID) VALUES ('08152');
INSERT INTO RC_E_1 (ID) VALUES ('02101');
INSERT INTO RC_E_1 (ID) VALUES ('10151');
INSERT INTO RC_E_1 (ID) VALUES ('13101');
INSERT INTO RC_E_1 (ID) VALUES ('09111');
INSERT INTO RC_E_1 (ID) VALUES ('13154');
INSERT INTO RC_E_1 (ID) VALUES ('04151');
INSERT INTO RC_E_1 (ID) VALUES ('08157');
INSERT INTO RC_E_1 (ID) VALUES ('09159');
INSERT INTO RC_E_1 (ID) VALUES ('08109');
INSERT INTO RC_E_1 (ID) VALUES ('06152');
INSERT INTO RC_E_1 (ID) VALUES ('09158');

生成每行 8 列，多出来的数据自动换到下一行

SELECT * 
FROM (

  -- ceil 函数为取整函数
  -- ceil(n/8) 则是将 序号 除以 8 取整，将组分成来，每组为 8 个
  -- mod 为取余函数
  -- mod(n-1,8) 则是取余，将 ceil 分好的组，分别排序，获得列名

  SELECT ceil(n/8) AS r, mod(n-1,8) AS c, ID FROM (

  	-- ROW_NUMBER() OVER (ORDER BY COLUMN_NAME) 
  	-- 把 ID 进行升序，然后获得排序的序号

    SELECT ID, ROW_NUMBER() OVER (ORDER BY ID) AS n FROM (
      SELECT DISTINCT ID  FROM RC_E_1
    )
  )
)

-- 这一段是行转列，c1,c2,c3,c4 ... 都是列名
-- 都是 mod 分配好的列
-- 由于 ceil 出来的 r 没有将他列出来，则多个相同的 r 会变成一行

PIVOT(
  MAX(ID) FOR c IN (
    '0' as c1,
    '1' as c2,
    '2' as c3,
    '3' as c4,
    '4' AS C5,
    '5' AS C6,
    '6' AS C7,
    '7' AS C8
  )
)

Title:Oracle-Row-Column-Conversion-1

Author:

Created:2022-01-05, 10:19:40

Updated:2023-04-07, 14:24:18

Full URL:http://yoursite.com/2022/01/05/Oracle-Row-Column-Conversion-1/

License: "CC BY-NC-SA 4.0" Keep Link & Author if Distribute.