Pivot columns to rows PL/SQL-CodePudding

I'm having data that I'm trying to pivot.

Input:

col01	col02	col03
1/2/3	John/Smith/David	2000/2500/1000

Output:

col01	col02	col03
1	John	2000
2	Smith	2500
3	David	1000

Thanks in advance.

CodePudding user response：

You can use:

WITH input_items (col01, col02, col03, c1st, c1end, c2st, c2end, c3st, c3end) AS (
  SELECT col01,
         col02,
         col03,
         1,
         INSTR(col01, '/', 1),
         1,
         INSTR(col02, '/', 1),
         1,
         INSTR(col03, '/', 1)
  FROM   input
UNION ALL
  SELECT col01,
         col02,
         col03,
         CASE c1end WHEN 0 THEN 0 ELSE c1end   1 END,
         CASE c1end WHEN 0 THEN 0 ELSE INSTR(col01, '/', c1end   1) END,
         CASE c2end WHEN 0 THEN 0 ELSE c2end   1 END,
         CASE c2end WHEN 0 THEN 0 ELSE INSTR(col02, '/', c2end   1) END,
         CASE c3end WHEN 0 THEN 0 ELSE c3end   1 END,
         CASE c3end WHEN 0 THEN 0 ELSE INSTR(col03, '/', c3end   1) END
  FROM   input_items
  WHERE  c1end > 0
  OR     c2end > 0
  OR     c3end > 0
)
SELECT CASE
       WHEN c1st = 0
       THEN NULL
       WHEN c1end = 0
       THEN SUBSTR(col01, c1st)
       ELSE SUBSTR(col01, c1st, c1end - c1st)
       END AS col01,
       CASE
       WHEN c2st = 0
       THEN NULL
       WHEN c2end = 0
       THEN SUBSTR(col02, c2st)
       ELSE SUBSTR(col02, c2st, c2end - c2st)
       END AS col02,
       CASE
       WHEN c3st = 0
       THEN NULL
       WHEN c3end = 0
       THEN SUBSTR(col03, c3st)
       ELSE SUBSTR(col03, c3st, c3end - c3st)
       END AS col03
FROM   input_items;

Or, with regular expressions (which are shorter to write but much less efficient to run):

WITH input_items (col01, col02, col03, v1, v2, v3, idx, max_idx) AS (
  SELECT col01,
         col02,
         col03,
         REGEXP_SUBSTR(col01 || '/', '(.*?)/', 1, 1, NULL, 1),
         REGEXP_SUBSTR(col02 || '/', '(.*?)/', 1, 1, NULL, 1),
         REGEXP_SUBSTR(col03 || '/', '(.*?)/', 1, 1, NULL, 1),
         1,
         GREATEST(
           REGEXP_COUNT(col01 || '/', '(.*?)/'),
           REGEXP_COUNT(col02 || '/', '(.*?)/'),
           REGEXP_COUNT(col03 || '/', '(.*?)/')
         )
  FROM   input
UNION ALL
  SELECT col01,
         col02,
         col03,
         REGEXP_SUBSTR(col01 || '/', '(.*?)/', 1, idx   1, NULL, 1),
         REGEXP_SUBSTR(col02 || '/', '(.*?)/', 1, idx   1, NULL, 1),
         REGEXP_SUBSTR(col03 || '/', '(.*?)/', 1, idx   1, NULL, 1),
         idx   1,
         max_idx
  FROM   input_items
  WHERE  idx < max_idx
)
SELECT v1 AS col1,
       v2 AS col2,
       v3 AS col3
FROM   input_items;

Which, for the sample data:

CREATE TABLE input(col01, col02, col03) AS
SELECT '1/2/3', 'John/Smith/David', '2000/2500/1000' FROM DUAL;

Both output:

COL01 COL02 COL03

1 John 2000

2 Smith 2500

3 David 1000

db<>fiddle here

CodePudding user response：

You can also use below query for that purpose. As you can see, it is using two regular functions (regexp_count and regexp_substr) to respectively count and extract all consecutive characters before each occurence of slash '[^/] ' character.

WITH Your_Input_Tab (col01, col02, col03) AS (
select '1/2/3', 'John/Smith/David', '2000/2500/1000' from dual 
UNION ALL
select '5/6', 'Chris/Kim', '2800/1570' from dual 
UNION ALL
select '8/9/10/11/12', 'Eric/Tidiane/Salim/Joe/Steven', '2700/1800/4000/2000/1450' from dual
)
select  regexp_substr(col01, '[^/] ', 1, level) col01
    ,   regexp_substr(col02, '[^/] ', 1, level) col02
    ,   regexp_substr(col03, '[^/] ', 1, level) col03
from Your_Input_Tab t
connect by level <= greatest (
                regexp_count(col01, '[^/] ')
            ,   regexp_count(col02, '[^/] ')
            ,   regexp_count(col03, '[^/] ')
        )
and prior col01 = col01
and prior sys_guid() is not null
;

demo on db<>fiddle

here is a link to a very detailed explanation of this approach