converting multiple comma separated columns into rows

Question

I have an Oracle table which holds comma separated values in many columns. For example :

Id  Column1 Column2
1   A,B,C   H
2   D,E     J,K
3   F       L,M,N

I want to split all the columns into rows and the output should be this :

ID  Column1 Column2
1      A       H
1      B       H
1      C       H
2      D       J
2      D       K
2      E       J
2      E       K
3      F       L
3      F       M
3      F       N

I found some suggestions which uses regexp_substr and connect by but it deals with only one column that has comma separated values. I have tried sub-query method also where I will be dealing with one column at a time in inner query and send the inner query output as input it outer query, this takes more time and the columns that hold comma separated values are more. So I cannot use the sub-query method.

MT0 · Accepted Answer · 2017-12-14 12:16:02Z

0

For one column you can CROSS JOIN a TABLE() collection expression containing a correlated sub-query that uses a hierarchical query to split the column value up into separate strings. For two columns, you just do the same thing for the second column and the CROSS JOIN takes care of ensuring that each delimited value in column1 is paired with each delimited value in column2.

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( Id, Column1, Column2 ) AS
SELECT 1, 'A,B,C', 'H' FROM DUAL UNION ALL
SELECT 2, 'D,E',   'J,K' FROM DUAL UNION ALL
SELECT 3, 'F',     'L,M,N' FROM DUAL;

Query 1:

SELECT t.id,
       c1.COLUMN_VALUE AS c1,
       c2.COLUMN_VALUE AS c2
FROM   table_name t
       CROSS JOIN
       TABLE(
         CAST(
           MULTISET(
             SELECT REGEXP_SUBSTR( t.Column1, '[^,]+', 1, LEVEL )
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT( t.Column1, '[^,]+' )
           ) AS SYS.ODCIVARCHAR2LIST
         )
       ) c1
       CROSS JOIN
       TABLE(
         CAST(
           MULTISET(
             SELECT REGEXP_SUBSTR( t.Column2, '[^,]+', 1, LEVEL )
             FROM   DUAL
             CONNECT BY LEVEL <= REGEXP_COUNT( t.Column2, '[^,]+' )
           ) AS SYS.ODCIVARCHAR2LIST
         )
       ) c2

Results:

| ID | C1 | C2 |
|----|----|----|
|  1 |  A |  H |
|  1 |  B |  H |
|  1 |  C |  H |
|  2 |  D |  J |
|  2 |  D |  K |
|  2 |  E |  J |
|  2 |  E |  K |
|  3 |  F |  L |
|  3 |  F |  M |
|  3 |  F |  N |

edited Dec 14, 2017 at 12:16

answered Dec 14, 2017 at 12:09

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bhavani Over a year ago

Thanks for immediate response but this works fine for 2 columns. I have many columns like this. Cross joining many times will have performance issue. Can you please suggest any other approach. :)

MT0 Over a year ago

@Bhavani If you want to do it without CROSS JOIN then you will need to specify a way that for each row a subset of the delimited values in column1 pair with a subset of values for column2 and pair with a subset of values for column3 otherwise, if you want to pair each delimited value in a row for column1 with each delimited value for column2 in the same row and pair it with each delimited value for column3 in that same row then what you want is a CROSS JOIN!

MT0 Over a year ago

@Bhavani Note - the table collection expressions are correlated back to the parent row so for ID=1 the cross join will be between 1 row for the parent and 3 rows for the sub-query for column1 and 1 row for the sub-query for column2. The correlation ensures that it will only generate the minimum number of rows required to pair each combination of delimited values.

arunb2w · Accepted Answer · 2017-12-14 12:15:03Z

-1

Below will give you an idea about to how to convert the comma separated string into rows. You can use this logic to satisfy your need.

select regexp_substr('a,b,c,v,f', '[^,]+',1,level)
from dual 
connect by level <= regexp_count('a,b,c,v,f', ',') + 1;

answered Dec 14, 2017 at 12:15

arunb2w

1,1961 gold badge11 silver badges29 bronze badges

2 Comments

Bhavani Over a year ago

Thank you. But this works only for one column that holds comma separated values.

arunb2w Over a year ago

can't you apply the same logic to the other column as well?

Collectives™ on Stack Overflow

converting multiple comma separated columns into rows

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related