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/0 9/2018
B add2 x1;y1;z1 03/12/2018;03/22/2018;05/0 9/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?
Records are
Name address Code Date
A add1 x;y;z 01/12/2018;02/22/2018;03/0
B add2 x1;y1;z1 03/12/2018;03/22/2018;05/0
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?
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.
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:
If you want it done in the SQL itself:
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
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)
)
/
ASKER
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.
Please add more sample data and expected results showing what you mean.
ASKER
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Change tab1 to your table name. I also changed "Date" to mydate because date is a reserved word.
Open in new window