Re: user-defined group function

From: Raiden Fighter <raiden_fighter_at_my-deja.com>
Date: Mon, 13 Sep 1999 13:51:05 GMT
Message-ID: <7rivg3$kdo$1@nnrp1.deja.com>

Hello,
  Thanks for the solution. It does do what I wanted but I am looking for a more general solution that works on character columns of ANY tables, not just table table_a, for example:

SQL>SELECT my_group_func(column_name) FROM USER_TABLES WHERE table_name ="SOME_TABLE"

my_group_func(column_name)

---

SOME_COLUMN1 SOME_COLUMN2 SOME_COLUMN3 *assume some_table has 3 columns*

Is it possible?
Thanks, Lynx

In article <37DB717E.79634D53_at_netscape.com>,   John Chiu <johnymc_at_netscape.com> wrote:
> (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
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Mon Sep 13 1999 - 08:51:05 CDT