oracle 中的字段数据行转列

oracle 中的字段数据行转列

1.oracle 中存在这样的数据，一个字段中，存放着多条数据的累积，但是是通过符号分割开，一般要用来关联的话，通常没有办法去拆分，无从下手，提供一个思路，

有着一个表，里面存在主键和单独的列，每个主键是唯一的，但是对应的col列的数据是一个的，或者是多个的，

准备 ：建表

WITH TMP AS
 (SELECT 11 PKID, ',25989-21,25708-40,' COL
    FROM DUAL
  UNION ALL
  SELECT 12 PKID, '2598-21,258-40,258-41,,258-41,' COL
    FROM DUAL
  UNION ALL
  SELECT 13 PKID, '25-21,25708-40' COL
    FROM DUAL
  UNION ALL
  SELECT 14 PKID, '26' COL
    FROM DUAL
  UNION ALL
  SELECT 15 PKID, '27' COL
    FROM DUAL
  UNION ALL
  SELECT 16 PKID, '20,' COL
    FROM DUAL
  UNION ALL
  SELECT 17 PKID, '28,' COL
    FROM DUAL
  UNION ALL
  SELECT 18 PKID, ',222,333,444,555,666,' COL
    FROM DUAL)

第一步 ：先找出对应的col列中有多少个分割的数据

WITH TMP AS
 (SELECT 11 PKID, ',25989-21,25708-40,' COL
    FROM DUAL
  UNION ALL
  SELECT 12 PKID, '2598-21,258-40,258-41,,258-41,' COL
    FROM DUAL
  UNION ALL
  SELECT 13 PKID, '25-21,25708-40' COL
    FROM DUAL
  UNION ALL
  SELECT 14 PKID, '26' COL
    FROM DUAL
  UNION ALL
  SELECT 15 PKID, '27' COL
    FROM DUAL
  UNION ALL
  SELECT 16 PKID, '20,' COL
    FROM DUAL
  UNION ALL
  SELECT 17 PKID, '28,' COL
    FROM DUAL
  UNION ALL
  SELECT 18 PKID, ',222,333,444,555,666,' COL
    FROM DUAL)
    
SELECT PKID, COL, LENGTH(COL) - LENGTH(REPLACE(COL, ',', '')) LT
          FROM TMP

第二步 ：借用树状中的 level，没有实际意义，但是可以用来做动态的查询

WITH TMP AS
 (SELECT 11 PKID, ',25989-21,25708-40,' COL
    FROM DUAL
  UNION ALL
  SELECT 12 PKID, '2598-21,258-40,258-41,,258-41,' COL
    FROM DUAL
  UNION ALL
  SELECT 13 PKID, '25-21,25708-40' COL
    FROM DUAL
  UNION ALL
  SELECT 14 PKID, '26' COL
    FROM DUAL
  UNION ALL
  SELECT 15 PKID, '27' COL
    FROM DUAL
  UNION ALL
  SELECT 16 PKID, '20,' COL
    FROM DUAL
  UNION ALL
  SELECT 17 PKID, '28,' COL
    FROM DUAL
  UNION ALL
  SELECT 18 PKID, ',222,333,444,555,666,' COL
    FROM DUAL)
SELECT PKID,
       COL,
       INSTR(COL, ',', B.LL - 1), -- 对应出现的开始位置
       INSTR(COL, ',', B.LL), -- 对应出现的结束位置
  FROM (SELECT PKID, COL, LENGTH(COL) - LENGTH(REPLACE(COL, ',', '')) LT
          FROM TMP) A,
       (SELECT LEVEL LL FROM DUAL CONNECT BY LEVEL <= 1000) B
 WHERE A.LT >= B.LL
 ORDER BY PKID

第三步 ：通过找到对应的位置后，开始截取

WITH TMP AS
 (SELECT 11 PKID, ',25989-21,25708-40,' COL
    FROM DUAL
  UNION ALL
  SELECT 12 PKID, '2598-21,258-40,258-41,,258-41,' COL
    FROM DUAL
  UNION ALL
  SELECT 13 PKID, '25-21,25708-40' COL
    FROM DUAL
  UNION ALL
  SELECT 14 PKID, '26' COL
    FROM DUAL
  UNION ALL
  SELECT 15 PKID, '27' COL
    FROM DUAL
  UNION ALL
  SELECT 16 PKID, '20,' COL
    FROM DUAL
  UNION ALL
  SELECT 17 PKID, '28,' COL
    FROM DUAL
  UNION ALL
  SELECT 18 PKID, ',222,333,444,555,666,' COL
    FROM DUAL)
SELECT PKID,
       COL,
       INSTR(COL, ',', B.LL - 1), -- 对应出现的开始位置
       INSTR(COL, ',', B.LL), -- 对应出现的结束位置
       REPLACE(SUBSTR(COL, INSTR(COL, ',', B.LL - 1), INSTR(COL, ',', B.LL)),
               ',',
               '') TEXT, -- 截取后的数据中不包含符号
       SUBSTR(COL, INSTR(COL, ',', B.LL - 1), INSTR(COL, ',', B.LL)) AS TEXTS -- 截取后所有的数据都在这
  FROM (SELECT PKID, COL, LENGTH(COL) - LENGTH(REPLACE(COL, ',', '')) LT
          FROM TMP) A,
       (SELECT LEVEL LL FROM DUAL CONNECT BY LEVEL <= 1000) B
 WHERE A.LT >= B.LL
 ORDER BY PKID

第四步 ：如果需要，将为空的，或者是为单独符号的都剔除

WITH TMP AS
 (SELECT 11 PKID, ',25989-21,25708-40,' COL
    FROM DUAL
  UNION ALL
  SELECT 12 PKID, '2598-21,258-40,258-41,,258-41,' COL
    FROM DUAL
  UNION ALL
  SELECT 13 PKID, '25-21,25708-40' COL
    FROM DUAL
  UNION ALL
  SELECT 14 PKID, '26' COL
    FROM DUAL
  UNION ALL
  SELECT 15 PKID, '27' COL
    FROM DUAL
  UNION ALL
  SELECT 16 PKID, '20,' COL
    FROM DUAL
  UNION ALL
  SELECT 17 PKID, '28,' COL
    FROM DUAL
  UNION ALL
  SELECT 18 PKID, ',222,333,444,555,666,' COL
    FROM DUAL)
SELECT PKID,
       COL,
       INSTR(COL, ',', B.LL - 1),
       INSTR(COL, ',', B.LL),
       REPLACE(SUBSTR(COL, INSTR(COL, ',', B.LL - 1), INSTR(COL, ',', B.LL)),
               ',',
               '') TEXT,
       SUBSTR(COL, INSTR(COL, ',', B.LL - 1), INSTR(COL, ',', B.LL)) AS TEXTS
  FROM (SELECT PKID, COL, LENGTH(COL) - LENGTH(REPLACE(COL, ',', '')) LT
          FROM TMP) A,
       (SELECT LEVEL LL FROM DUAL CONNECT BY LEVEL <= 1000) B
 WHERE A.LT >= B.LL
   AND REPLACE(SUBSTR(COL, INSTR(COL, ',', B.LL - 1), INSTR(COL, ',', B.LL)),
               ',',
               '') IS NOT NULL
 ORDER BY PKID

结束，将对应的临时表换成你对应的表，需要的字段添加即可