Home » SQL & PL/SQL » SQL & PL/SQL » row records into one column (10.0.1.1)
row records into one column [message #410434] Sat, 27 June 2009 00:27 Go to next message
mrpranab
Messages: 32
Registered: March 2005
Member
Hi,

I have some row records which needs to be convert in a single column like the below example. The 3 columns are source, period and ID.

Input:->
Source---Period---ID
A--- 2008--- 1
A--- 2008--- 2
A--- 2008--- 3
B--- 2008--- 4
B--- 2008--- 5
B--- 2008--- 6
A--- 2008--- 7
B--- 2008--- 8

Output:->
Source--- Period--- ID
A--- 2008--- 1,2,3,7
B--- 2008--- 4,5,6,8

How can I achieve the above output?

Thanks in adv.
Re: row records into one column [message #410437 is a reply to message #410434] Sat, 27 June 2009 00:49 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Search the Forum/Google for STRAGG / PIVOT.

By
Vamsi
Re: row records into one column [message #410441 is a reply to message #410434] Sat, 27 June 2009 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also please read OraFAQ Forum Guide, especially "How to format your post?" section.
Use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: row records into one column [message #410521 is a reply to message #410441] Sun, 28 June 2009 00:02 Go to previous messageGo to next message
srraajesh
Messages: 63
Registered: May 2005
Member
May be this helps.
create table temp
(
source_1 varchar2(2),
period varchar2(4),
id_l number
)

and all your test data inside this table..the SQL
select source_1,period,
rtrim(replace(replace(xmlagg(xmlelement("a",id_l)).getstringval(),'<a>',NULL),'</a>',','),',') 
from temp
group by source_1,period

gives the output that you asked for..
A	2008	1,2,3,7
B	2008	4,5,6,8

[Updated on: Mon, 29 June 2009 00:49] by Moderator

Report message to a moderator

Re: row records into one column [message #410566 is a reply to message #410434] Mon, 29 June 2009 00:28 Go to previous messageGo to next message
ds285269
Messages: 10
Registered: June 2009
Location: Mumbai
Junior Member
Hi,
I think following script will helpful to u,
Let me know if any modification u will find.

Function:-

create or replace function sample_func(x varchar2) return varchar2 is
cursor c1 is select * from sample where a like x;
id1 varchar2(25);
begin
for i in c1 loop
id1:=id1||','||i.id;
end loop;
id1:=ltrim(id1,',');
return(id1);
end;

Query:-
select a,year, sample_func(a) from sample group by a,year;

cheers
srini.
Re: row records into one column [message #410570 is a reply to message #410566] Mon, 29 June 2009 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"u" will not answer you as it is not a member of the forum.
Please read OraFAQ Forum Guide and post accordingly.

Regards
Michel
Re: row records into one column [message #410689 is a reply to message #410434] Mon, 29 June 2009 14:08 Go to previous message
mrpranab
Messages: 32
Registered: March 2005
Member
Hi All,

I am getting the desired output now. Thanks to all of you for
your solutions.

Thank you.
Previous Topic: query optimization
Next Topic: Procedure execution hierarchy
Goto Forum:
  


Current Time: Fri Dec 09 03:53:02 CST 2016

Total time taken to generate the page: 0.08892 seconds