• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 30
  • Last Modified:

convert comma separated values to different rows

Hi,

I have rows like below.

role                                                                                                                        ID
-------
Cloud Success Manager,Enterprise Cloud Architect                                     6620A17C28DBD796E0530C37548C2D68
virtual,Town Hall                                                                                                  66209194F2307DB3E0530C37548C276E


I want to convert it like below rows

Enterprise Cloud Architect                         6620A17C28DBD796E0530C37548C2D68
Cloud Success Manager                              6620A17C28DBD796E0530C37548C2D68
Town Hall                                                       66209194F2307DB3E0530C37548C276E
virtual                                                              66209194F2307DB3E0530C37548C276E

How do I achieve this?
0
sakthikumar
Asked:
sakthikumar
1 Solution
 
slightwv (䄆 Netminder) Commented:
Try this (replace tab1 with your table name):
select
	regexp_substr(role,
					'[^,]+',
					1,
					column_value)
		role, id
from tab1,
	table(select collect(level)
				from dual
			connect by level <= length(role) - length(replace(role, ',')) + 1)
/

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now