I'm trying to create a row for each person, str but I am getting extra output.
Can someone please explain what I did wrong and show me how to fix it.
Below is my test CASE and expected results. Thanks to all who answer and your expertise.
with rws as (
select 'Bob' person, 'AB,CR,DE' str from dual UNION ALL
select 'Jane' person, 'AB' str from dual
)
select person,
regexp_substr (
str,
'[^,]+',
1,
level
) value
from rws
connect by level <=
length ( str ) - length ( replace ( str, ',' ) ) + 1
ORDER BY person, str;
PERSON VALUE
Bob AB
Bob CR
Bob DE
Bob DE
Bob CR
Jane AB
Expected results
PERSON VALUE
Bob AB
Bob CR
Bob DE
Jane AB