could you please solve the problem write a query
input table t1
sno|sdata
123|abc123,abc456,bcd789
123|def1011,xyz123
I want this output
sno|sdata
123|abc123
123|abc456
123|bcd789
123|def1011
123|xyz123
please give idea to resolve the query as early as possible
Classic, more or less.
SQL> with test (sno, sdata) as
2 (select 123, 'abc123,abc456,bcd789' from dual union
3 select 123, 'def1011,xyz123' from dual
4 )
5 select
6 sno,
7 regexp_substr(sdata, '[^,]+', 1, column_value) sdata
8 from test,
9 table(cast(multiset(select level from dual
10 connect by level <= regexp_count(sdata, ',') + 1
11 ) as sys.odcinumberlist));
SNO SDATA
---------- --------------------
123 abc123
123 abc456
123 bcd789
123 def1011
123 xyz123
SQL>
[EDIT, to show what happens if ...]
... you omit lines 9 - 11.
Basically, COLUMN_VALUE references LEVEL (from line 9). If you rewrite it so that query doesn't use any of these, you'd get duplicates:
SQL> with test (sno, sdata) as
2 (select 123, 'abc123,abc456,bcd789' from dual union
3 select 123, 'def1011,xyz123' from dual
4 )
5 select
6 sno,
7 regexp_substr(sdata, '[^,]+', 1, level) sdata
8 from test
9 connect by level <= regexp_count(sdata, ',') + 1
10 order by 1, 2;
SNO SDATA
---------- --------------------
123 abc123
123 abc456
123 abc456
123 bcd789
123 bcd789
123 bcd789
123 bcd789
123 def1011
123 xyz123
123 xyz123
10 rows selected.
SQL>
One way to avoid them is to use DISTINCT (which would do the job, but that's wrong), or use syntax I showed you in the first answer to your question.
