Dividing comma separated strings into columns I have a problem with dividing comma separated values. I have 2 columns with comma separated values, for ex:

ID        Name 
1,2,3     Ab,cd,ef


I want the columns divided as:

Id  Name
1   ab
2   cd
3   ef


I used:

 xmltable('r/c' passing xmltype('&lt;r&gt;&lt;c&gt;' || replace(ID,',','&lt;/c&gt;&lt;c&gt;') || '&lt;/c&gt;&lt;/r&gt;')
          columns ID_NEW varchar2(400) path '.') &lt;br/&gt;


Output was:

ID      Name 
1       ab,cd,ef
2       ab,cd,ef
3       ab,cd,ef


I'm using Oracle 10g.
Is this what you're looking for? I'm using regexp_substr

with test as
(select '1,2,3' col1, 'Ab,cd,ef' col2 from dual)
  select regexp_substr(col1, '[^,]+', 1, rownum) result1, regexp_substr(col2, '[^,]+', 1, rownum) result2
  from test
  connect by level &lt;= length(regexp_replace(col1, '[^,]+')) + 1;


This is the SQL Fiddle.