Please refer to the LiveSQl link for table creation and how the data is within the table. Is there a direct way to achieve this using SQL. I am aware of commands to split for each record so that Cartesian output is created but not sure if something like below is possible to do. Thanks!
Current Table
===========
ID Name Comments
1 ABC Test1; Test2:Test3
1 XYZ Test1; Test2:Test3
Required Output
=============
ID Name Comments
1 ABC Test1
1 XYZ Test2
1 Test3
So you want to:
- Convert the comments to rows
- For each of these generated rows, link to one of the existing table rows
- If there's more rows from the comments than in the table, fill down the IDs
?
If so, you can do something like:
- Split the comments to rows; assign a row number to each distinct value using dense_rank()
- Assign a row_number to each row in the table
- Partition outer join by ID the table to the generated rows. This will fill down the IDs for you:
Create table testdrop(ID Number,Name Varchar2(10), Comments Varchar2(100));
insert into testdrop values (1,'ABC','Test1; Test2:Test3');
insert into testdrop values (1,'XYZ','Test1; Test2:Test3');
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;
ID NAME ERROR
1 ABC Test1
1 XYZ Test2
1 Test3
For more on partitioned outer joins, read this by Adrian Billington:
http://www.oracle-developer.net/display.php?id=312