0

I have the following table

mydata:

ID  NUMBERS
1   1,2
2   1,2,3,4,5
3   1,2,3

My ultimate goal is to generate the following result: 

ID  Num
1   1
1   2
2   1
2   2
2   3
2   4
2   5
3   1
3   2
3   3

e.g. all the combinations for each id with a corresponding numbers

now... I came up with the following query 

with mydata as (
select 1 as id, '1,2' as numbers from dual
union
select 2 as id, '1,2,3,4,5' as numbers from dual
union 
select 3 as id, '1,2,3' as numbers from dual
)
SELECT distinct  id,TRIM(REGEXP_SUBSTR(numbers, '[^,]+', 1, level)) lv
   FROM (
    select id, numbers
    from mydata 
  )
    CONNECT BY  level <= REGEXP_COUNT(numbers, '[^,]+')
  order by id

Without the distinct - the above query gives 66 repetitive combinations instead of 10 combinations needed (so for large data set - it will get slow..)

I wonder, is it a correct way to achieve this and is there any better and more efficient way to get the desired result.

Thanks.

PKey
  • 3,715
  • 1
  • 14
  • 39
  • That should help you https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle . I'm marking it as duplicate. – g00dy Jul 13 '17 at 06:18
  • @g00dy ok thanks. For the record - Nefreo's solution worked for me quite well. – PKey Jul 13 '17 at 06:47

0 Answers0