Re: How to show a select in one row

create or replace function col_to_row
return varchar2 as

   var_out varchar2(2000) := null;
begin

   for curs in

       (select id from toto order by id )    loop

         var_out := var_out||curs.id;
   end loop;
return var_out;
end col_to_row;

select col_to_row
from dual;

Neil

Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote in message news:37D51FA8.AB098C7A_at_edcmail.cr.usgs.gov...
> What you want to do is to turn a table on it's side. This can be found
> in Oracle, The Complete Reference by Oracle Press. To do this, you need
> to use the DECODE function. But with the DECODE function, you need to
> know the possible values that you might return. For instance,
>
> SELECT DECODE(id,'1','1',NULL) AS "1",
> DECODE(id,'2','2',NULL) AS "2",
> DECODE(id,'3','3',NULL) AS "3"
> FROM toto;
>
> would return
>
> 1 2 3
> --- --- ---
> 1
> 2
> 3
>
>
> This isn't quite what you are looking for, but it shows some of the
> "power" of the decode statement (as well as it's weaknesses).
>
> HTH,
> Brian
>
>
> Jorge wrote:
> >
> > something like this:
> > select * from toto
> > id
> > ---
> > 1
> > 2
> > 3
> >
> > select * from toto
> > id
> > ---
> > 123
Received on Fri Sep 10 1999 - 14:30:49 CDT