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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: PL/SQL package
Next Topic: checking contents of a variable
Goto Forum:
  


Current Time: Thu Mar 28 14:35:47 CDT 2024