CODEWITHSUNDEEP
sqloracle-database
Geek
Geek

Reputation: 3329

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.

Upvotes: 0

Views: 302

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271111

You can use regexp_substr):

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

Here is a db<>fiddle.

Upvotes: 2

Related Questions