-5

My sample data

Table A

col1    col2
------------------------------- 
1       1,2,3
2       1,2,4,5

Required Output is

col1  col2
-------------------------
1     1
1     2
1     3
2     1
2     2
2     4   
2     5

How can I get the desired output in oracle? Thanks in advance

Sudhirson
  • 1
  • 2
  • we can help you, if you have done something so far – Ravi Jan 23 '18 at 04:29
  • Thank you Andam. But how can I do this in Oracle? – Sudhirson Jan 23 '18 at 04:50
  • Have you searched in other peoples posts? If not please search first. – Andam Jan 23 '18 at 05:13
  • Thank you Kaushik Nayak. – Sudhirson Jan 23 '18 at 06:59
  • By _"get the required output"_ it sounds like you meant _"parse comma-separated string into multiple rows"_. [Googling for that](https://www.google.co.uk/search?q=oracle+parse+comma-separated+string+into+multiple+rows) gives me 224K results according to Google, with https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle at the top. – William Robertson Jan 23 '18 at 18:14

1 Answers1

0

Here's one option:

SQL> with test (col1, col2) as
  2  (select 1, '1,2,3' from dual union
  3   select 2, '5,6,7,8' from dual
  4  )
  5  select col1, regexp_substr(col2, '[^,]+', 1, column_value) col2
  6  from test,
  7       table(cast(multiset(select level from dual
  8                           connect by level <= regexp_count(col2, ',') + 1)
  9                  as sys.odcinumberlist))
 10  order by col1, 2;

      COL1 COL2
---------- -------
         1 1
         1 2
         1 3
         2 5
         2 6
         2 7
         2 8

7 rows selected.

SQL>
Littlefoot
  • 131,892
  • 15
  • 35
  • 57