0

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.

Bhavani
  • 195
  • 1
  • 10

2 Answers2

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 |
MT0
  • 143,790
  • 11
  • 59
  • 117
  • 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. :) – Bhavani Dec 14 '17 at 12:21
  • 1
    @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 Dec 14 '17 at 12:35
  • @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. – MT0 Dec 14 '17 at 12:38
-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;
arunb2w
  • 1,196
  • 9
  • 28