SQL & PL/SQL :: Extract Correct Value For Child Records

Test case: drop table test;

create table test (id number, last_name varchar2(15), first_name varchar2(15), empno varchar2(15))

select * from test;

insert into test values (143,'frank','kadel,watson','j2098,k09876');
insert into test values (143,'steve','kadel,watson','j0987i,kuy765');
[code]....

The requirement is as follows: I need to split the rows by first_name and assign the respective empno in the results child rows if there is any.

For example:- Where id = 143, the resultset should be like this.

ID LAST_NAME FIRST_NAME EMPNO
---------- --------------- --------------- ---------------
143 frank kadel j2098
143 frank watson k09876
143 steve kadel j0987i
143 steve watson kuy765

sofar, i am able to come with the query to split the records by last name but unable to find the way to extract the respective empno and assign to the splited records correctly.

SQL> select id, last_name, EXTRACTVALUE(x.COLUMN_VALUE, 'e') first_name, empno
2 from test,
3 TABLE(XMLSEQUENCE(XMLTYPE('<e><e>' || REPLACE(first_name, ',', '</e><e>') || '</e></e>')
4 .EXTRACT('e/e'))) x
5 ;
[code]...