multiple row into single row

[pras]

hi everybody,

i have a table in which there are say n no of rows..now i want to concate that rows into a single row..for e.g

SQL> select mailmessage from tempinsert;

MAILMESSAGE
------------------------------------------------------------------
1,I101,,,
2,i102,,,

now i want the o/p as

1,I101,,, 2,i102,,,
as a single row as output...how do i do this...pls help

thanks in advance
pras

[skid]

Hi!

I think you have to do it in PL/SQL, create a function which concatenate the columns somewhat like (not tested) :

create function concat_this
as
v_concat varchar2(2000);
begin
for rec in (select col1 from tablename)
loop
v_concat := v_concat||rec.col1;
end loop;
return v_concat;
end;


hth.

[balajiyes]

SYntax was incorrect:
this was missing "return varchar2"

create function concat_this return varchar2
as
v_concat varchar2(2000);
begin
for rec in (select ename from emp)
loop
v_concat := v_concat||rec.ename;
end loop;
return v_concat;
end;

[sona]

you can use coalesce function if you use Oracle9i.
select coalesce(columnname||',' , '') from table

I have heard of TABLE_TO_COMMA package .
But i haven't tried it.
Have a look on it.