r2c: Row to Col

Lets see what solution can be given when it comes to pulish a complete column from a table in one row, with some delimiter.

Prerequisite:

create table user_mst
(
 user_uid varchar2(10)
 ,user_name varchar2(50)
);

Basic Function:

create or replace function r2c
(
        in_select_statement in varchar2, --pass a select statement with a single column eg: select user_uid from user_mst
        in_result_separator in varchar2 default ',' -- sperator for the output default it is comma
)
     return clob
     authid current_user
     as
     type details is ref cursor;
     lc_str clob;
     lc_colval clob;
     c_dummy details;
     begin
     open c_dummy for in_select_statement;
     loop
         fetch c_dummy into lc_colval;
         exit when c_dummy%notfound;
         lc_str := lc_str || in_result_separator || lc_colval;
     end loop;
     close c_dummy;
     return substr(lc_str,2);
     exception
     when others then
     lc_str := sqlerrm;
        if c_dummy%isopen then
         close c_dummy;
     end if;
     return lc_str;
     end;
/

How to Execute:
select r2c('select user_uid from user_mst',',') from dual;
 

For any other queries you can drop me an email on ps.lall@me.com.