Create table testdrop(ID Number,Name Varchar2(10), Comments Varchar2(100))
Table created.
insert into testdrop values (1,'ABC','Test1; Test2:Test3')
1 row(s) inserted.
insert into testdrop values (1,'XYZ','Test1; Test2:Test3')
1 row(s) inserted.
select * from testdrop
ID | NAME | COMMENTS | 1 | ABC | Test1; Test2:Test3 | 1 | XYZ | Test1; Test2:Test3 |
---|
insert into testdrop values (2,'DFG','T1;T2');
insert into testdrop values (2,'PQR','T1;T2');
insert into testdrop values (2,'LMN','T1;T2');
insert into testdrop values (2,'RST','T1;T2');
select * from testdrop;
with rws as (
select
distinct
trim(regexp_substr(t.comments, '[^;:]+', 1, levels.column_value)) as error,
dense_rank() over (order by trim(regexp_substr(t.comments, '[^;:]+', 1, levels.column_value))) rn
from
testdrop t,
table(cast(multiset(select level from dual connect by level <= length (regexp_replace(t.comments, '[^;:]+')) + 1) as sys.OdciNumberList)) levels
), t as (
select t.*, row_number() over (order by id, name) rn from testdrop t
)
select id, name, error from rws
left join t partition by (id)
on rws.rn = t.rn
order by rws.rn;