Home » SQL & PL/SQL » SQL & PL/SQL » one column into multiple rows
one column into multiple rows [message #37793] |
Thu, 28 February 2002 06:53 |
Ravi
Messages: 251 Registered: June 1998
|
Senior Member |
|
|
Hi,
I have a questions about splitting one column into multiple rows. But, here is my exact requirement,
Example:
Column A has value: 100,200
Column B has value: xx,yy
I wanted the output as 100,xx in one row and
200,yy in the next row.
Also some times I may get a record with
Column A has value: <null>
Column B has value: xx,yy
in this case also I need to split the record into two rows as,
null, xx
null, yy
Also some times I may get a record with
Column A has value: 100,200
Column B has value: <null>
in this case also I need to split the record into two rows as,
100,null
200,null
Could any body give me some example on how to achieve this goal, that would be great.
Thanks in advace.
Ravi
|
|
|
Re: one column into multiple rows [message #37797 is a reply to message #37793] |
Thu, 28 February 2002 07:21 |
pratap kumar tripathy
Messages: 660 Registered: January 2002
|
Senior Member |
|
|
here is the example
create table ravi( a varchar2(10),b varchar2(10));
insert into ravi values('100,200','xx,yy');
insert into ravi values(null,'aa,bb');
insert into ravi values('555,666',null);
select substr(a,1,instr(a,',')-1),substr(b,1,instr(b,',')-1) from ravi
union
select substr(a,instr(a,',')+1),substr(b,instr(b,',')+1) from ravi
/
|
|
|
Re: one column into multiple rows [message #37814 is a reply to message #37793] |
Thu, 28 February 2002 15:09 |
Ravi
Messages: 251 Registered: June 1998
|
Senior Member |
|
|
Hi Pratap,
When I did following stuff, I am getting 8 records instead of 6. Remaining two are blank records. Can you pl write me how do I overcome this. Thanks again for your help.
create table ravi( a varchar2(10),b varchar2(10),c varchar2(10));
insert into ravi values('100,200','xx,yy','test');
insert into ravi values(null,'aa,bb','test');
insert into ravi values('555,666',null,'test');
insert into ravi values(null,null,null);
insert into ravi values(null,null,'test');
select c,substr(a,1,instr(a,',')-1),substr(b,1,instr(b,',')-1) from ravi
union
select c,substr(a,instr(a,',')+1),substr(b,instr(b,',')+1) from ravi
/
|
|
|
Re: one column into multiple rows [message #37823 is a reply to message #37814] |
Fri, 01 March 2002 00:03 |
pratap kumar tripathy
Messages: 660 Registered: January 2002
|
Senior Member |
|
|
try this
---------
select c,substr(a,1,instr(a,',')-1),substr(b,1,instr(b,',')-1) from ravi
where a is not null or b is not null
union
select c,substr(a,instr(a,',')+1),substr(b,instr(b,',')+1) from ravi
where a is not null or b is not null
/
and your next is question was regarding bad data
search the data for more than 2- comma and reject the data if it is so.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 05:12:48 CDT 2024
|