Re: user-defined group function

(1) create the function as follows:

Create or replace function my_group_func(id number) return varchar2 as
 cursor cur_tablea(input_id) is

      select name from table_a where id = input_id; result varchar2(500);

• I use 500 but you can make it bigger as long as -- your version of Oracle supports(2000/4000 bytes) begin for currow in cur_tablea(id) loop result := result || currow.name; end loop; return result; end; /
  (2) in sqlplus:
  

select distinct id, my_group_func(id) "Concat" from table_a;

it should come up with what you want.

John Chiu
johnc_at_relsol.com
johnymc_at_netscape.net Received on Sun Sep 12 1999 - 04:25:18 CDT