CODEWITHSUNDEEP
oracle-databasesplit
Anonymous S
Anonymous S

Reputation: 3

multiple columns' row split in oracle

I got a single string row split into rows

For example,

A,B,C,D,E

into

A
B
C
D
E

but what I would like to is multiple columns' single row

| A,B,C | H,I,J,K,L | Q,R,X,Y,Z |

into

A | H | Q
B | I | R
C | J | X
  | K | Y
  | L |

How can I do this in oracle?

Upvotes: 0

Views: 180

Answers (1)

Popeye
Popeye

Reputation: 35900

You can use hiearchy query as follows:

SQL> WITH DATAA ( D ) AS (
  2      SELECT '| A,B,C | H,I,J,K,L | Q,R,X,Y,Z |'
  3      FROM DUAL
  4  )
  5  -- your query starts from here
  6  SELECT TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(D.D, '[^|]+', 1, 1), '[^,]+', 1, LEVEL)) AS COL1,
  7         TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(D.D, '[^|]+', 1, 2), '[^,]+', 1, LEVEL)) AS COL2,
  8         TRIM(REGEXP_SUBSTR(REGEXP_SUBSTR(D.D, '[^|]+', 1, 3), '[^,]+', 1, LEVEL)) AS COL3
  9    FROM DATAA D
 10  CONNECT BY LEVEL <= (
 11          SELECT MAX(REGEXP_COUNT((REGEXP_SUBSTR(D.D, '[^|]+', 1, COLUMN_VALUE)), ',')) + 1
 12            FROM DATAA D
 13              CROSS JOIN TABLE ( CAST(MULTISET(
 14                  SELECT LEVEL LVL
 15                    FROM DUAL
 16                  CONNECT BY LEVEL <= REGEXP_COUNT(D.D, '[^|]+')
 17              ) AS SYS.ODCIVARCHAR2LIST) ) LVLS
 18      );

COL1    COL2        COL3
------- ----------- -----------
A       H           Q
B       I           R
C       J           X
        K           Y
        L           Z

SQL>

Upvotes: 2

Related Questions