row records into one column [message #410434] |
Sat, 27 June 2009 00:27  |
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 #410521 is a reply to message #410441] |
Sun, 28 June 2009 00:02   |
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   |
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.
|
|
|
|
|