Home » SQL & PL/SQL » SQL & PL/SQL » multiple rows in one column (DB10g , XP)  () 1 Vote
multiple rows in one column [message #352235] Mon, 06 October 2008 14:26 Go to next message
skoskos
Messages: 19
Registered: November 2005
Location: Hell(as)
Junior Member
Hi ,
I have a string such as : 'xx,yyyy,zzzzzzzzzz' and i want to get the following:

str
-------
xx
yyy
zzzzzzzzz

without using regexp_substr(Oracle Forms6i does not allow it) nor pipelined function(the string is produced at the client -side and is used only on client-side , so there will be an overhead if i write a db function...).
Is there any other way...???

Thanks...
Sim
Re: multiple rows in one column [message #352236 is a reply to message #352235] Mon, 06 October 2008 14:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Search for "rows to columns", "unpivot" or "string list".

Regards
Michel
Re: multiple rows in one column [message #352246 is a reply to message #352235] Mon, 06 October 2008 21:05 Go to previous messageGo to next message
prt1979
Messages: 3
Registered: October 2008
Location: ZhengZhou China
Junior Member
u can execute sql here:

create or replace type type_split as table of varchar2(50);

create or replace function split
(
p_list varchar2,
p_sep varchar2 := ','
) return type_split pipelined
is
l_idx pls_integer;
v_list varchar2(50) := p_list;
begin
loop
l_idx := instr(v_list,p_sep);
if l_idx > 0 then
pipe row(substr(v_list,1,l_idx-1));
v_list := substr(v_list,l_idx+length(p_sep));
else
pipe row(v_list);
exit;
end if;
end loop;
return;
end split;

select * from table(split('Koreas,are,two,countries',','));

[Updated on: Mon, 06 October 2008 21:10]

Report message to a moderator

Re: multiple rows in one column [message #352256 is a reply to message #352235] Mon, 06 October 2008 22:03 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

@ prt1979 ,

Welcome to Orafaq world .

Please read Orafaq Guidelines before the next posting.

And OPs requrement was


Quote:
without using regexp_substr(Oracle Forms6i does not allow it) nor pipelined function



Moreover , this is achievable through straight SQL also .

Thumbs Up
Rajuvan

[Updated on: Mon, 06 October 2008 22:04]

Report message to a moderator

Re: multiple rows in one column [message #352450 is a reply to message #352256] Tue, 07 October 2008 13:46 Go to previous messageGo to next message
skoskos
Messages: 19
Registered: November 2005
Location: Hell(as)
Junior Member
"Moreover , this is achievable through straight SQL also ."
I would be grateful ... if you could write it down.....
Both the Oracle forums , Orafaq forums are full of threads regarding rows to columns(using case,decode... hierarchical queries , e.t.c.) but only very few regarding my case...
I found one applicable with the use of regexp_substr ...but , the Oracle tool i use does not allow it......!!!!


Thanks , all for the contribution....!!!
Sim
Re: multiple rows in one column [message #352486 is a reply to message #352450] Wed, 08 October 2008 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I would be grateful ... if you could write it down.....
Both the Oracle forums , Orafaq forums are full of threads regarding rows to columns(using case,decode... hierarchical queries , e.t.c.) but only very few regarding my case...

What is the difference between your case and the other ones?

Regards
Michel
Re: multiple rows in one column [message #352627 is a reply to message #352486] Wed, 08 October 2008 11:31 Go to previous messageGo to next message
skoskos
Messages: 19
Registered: November 2005
Location: Hell(as)
Junior Member
You have quoted it.....!!!!
"Both the Oracle forums , Orafaq forums are full of threads regarding rows to columns..."

... but i want multiple strings(in one column) seperated with commas to many rows...

Here is the difference :
Not this....
Col        Col1  Col2  Col3
xxx     -> xxx   yyy   zzz
yyy
zzz

but this:
Col
xxx,yyy,zzz   

->

Col
xxx
yyy
zzz

Thanks....
Sim

[Updated on: Wed, 08 October 2008 11:32]

Report message to a moderator

Re: multiple rows in one column [message #352631 is a reply to message #352627] Wed, 08 October 2008 11:53 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I also said "string list".
You can add "values in a list" or the like.

Regards
Michel
Previous Topic: Adding in a MAX() function
Next Topic: Adding Column Values
Goto Forum:
  


Current Time: Wed Dec 07 22:03:57 CST 2016

Total time taken to generate the page: 0.07092 seconds