Link to home
Start Free TrialLog in
Avatar of Rajesh Kumar
Rajesh Kumar

asked on

Split the values with semicolon in multiple rows

I have four(Name, address, code, Date ) columns in a table
Records are
Name    address      Code              Date
A            add1            x;y;z                01/12/2018;02/22/2018;03/09/2018
B            add2            x1;y1;z1          03/12/2018;03/22/2018;05/09/2018
Output should be
A         add1        x                        01/12/2018
                             y                        02/22/2018
                             z                        03/09/2018
B        add2         x1                      03/12/2018
                            y1                       03/22/2018
                            z1                       05/09/2018

How to achieve the output like in above by oracle sql?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I made the assumption there is a 1-to-1 relation between code and date.  Such that if code has X values, there are X dates to go with it.

Change tab1 to your table name.  I also changed "Date" to mydate because date is a reserved word.
select name, address,
	regexp_substr(code, '[^;]+', 1, column_value) code,
	regexp_substr(mydate, '[^;]+', 1, column_value) mydate
from tab1,
	table(select collect(level)
				from dual
			connect by level <= length(code) - length(replace(code, ';')) + 1)
/

Open in new window

Avatar of Rajesh Kumar

ASKER

out put is
A         add1        x                        01/12/2018
A         add1        y                        02/22/2018
A         add1        z                        03/09/2018
B         add2        x1                       03/12/2018
B         add2        y1                       03/22/2018
B         add2        z1                       05/09/2018

which is incorrect.
If you mean incorrect because it doesn't break and repeats names and addresses it is simple enough.

If you mean something else, please explain.

If you are using sqlplus, SQL Developer or a tool that recognizes sqlplus reporting, then it is really simple:
clear breaks
break on name on address
select ...--the rest of the select above

Open in new window


If you want it done in the SQL itself:
select
	case when rn=1 then name end name,
	case when rn=1 then address end address,
	code,
	mydate
from(
	select
		row_number() over(partition by name order by name) rn,
		name, address,
		regexp_substr(code, '[^;]+', 1, column_value) code,
		regexp_substr(mydate, '[^;]+', 1, column_value) mydate
	from tab1,
		table(select collect(level)
				from dual
			connect by level <= length(code) - length(replace(code, ';')) + 1)
)
/

Open in new window

Your solution is good only for one name, If we have more than one name, address, code date then this will show all name and address blank except first row.
I don't understand what you mean.

Please add more sample data and expected results showing what you mean.
when I execute your sql The output was like below
A         add1        x                        01/12/2018
                             y                        02/22/2018
                             z                        03/09/2018
                            x1                       03/12/2018
                            y1                       03/22/2018
                            z1                       05/09/2018

But i need the the out like below

A         add1        x                        01/12/2018
                             y                        02/22/2018
                             z                        03/09/2018
B         add2        x1                       03/12/2018
                             y1                       03/22/2018
                             z1                       05/09/2018
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial