Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Multiple columns -> comma-seperated list in one column

Re: Multiple columns -> comma-seperated list in one column

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Thu, 28 Oct 1999 18:34:23 +0200
Message-ID: <7v9u0j$6pb$1@oceanite.cybercable.fr>


You can make it with a function:

create or replace function f(id varchar2) return varchar is

   result varchar2(32000) := '';
   cursor curs is select col2 from <table> where col1 = id;    first boolean := true;
begin

   for rec in curs loop

      if first then
         result := rec.col2;
         first := false;
      else
         result := result || ', ' || rec.col2;
      end if;

   end loop;
   return result;
end;
/

and then:

select col1, f(col1) from <table> group by col1;

--
Have a nice day
Michel

Adam Hasselbalch Hansen <di991284_at_diku.dk> a écrit dans le message : Pine.OSF.4.20.9910281658420.3860-100000_at_bure.diku.dk...
> Hi....
>
> I need a little help.
>
> Lets say I have a table with the following values:
>
> COL1 COL2
> --------- ---------
> VAL11 VAL21
> VAL11 VAL22
> VAL11 VAL23
> VAL11 ...
> VAL12 VAL21
> VAL12 VAL24
> VAL12 ...
> ... ...
>
> Now, what I want to do, is to see it as follows:
>
> COL1 COL2
> --------------- ------------------------
> VAL11 VAL21, VAL22, VAL23, ...
> VAL12 VAL21, VAL24, ...
> ... ...
>
> Anybody have any idea how to do that, using a simple SQL or PL/SQL
> statement?
>
> Kind Regards
> Adam
>
Received on Thu Oct 28 1999 - 11:34:23 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US