Oracle SQL query to get comma-separated string in single query

I have a data which is represented as below:

Name     parent_unit child_unit
--------------------------------
aa       1           0
aa,bc    1           1
aa,de    1           2
bb       2           0
bb,ab    2           1

I have a query as follows which has to be tweaked to get parent and child names respectively

select u.name,u.id, lk.name as parentName, lk.name as childName
from users u, users_unit uu, lk_unit lk
where u.id = uu.user_id
  and uu.parent_unit = lk.parent_unit
  and uu.child_unit = lk.child_unit

My output should look as follows:

name id parentName childName
----------------------------
X    1  aa
Y    2  aa         bc
Z    3  bb         ab

I basically want to split the lk.name based on seperator (,) and 1st string before seperator is parentName and 2nd string after seperator is childName. If there are no seperators then its just the parentName.

Answer

You can use regexp_substr):

select regexp_substr(name, '[^,]+', 1, 1) as parent_name,
   regexp_substr(name, '[^,]+', 1, 2) 

Here is a db<>fiddle.