Re: How to write a single-group group function

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 17 Oct 1999 10:29:11 +0100
Message-ID: <940152606.3029.0.nnrp-12.9e984b29@news.demon.co.uk>

This is a far from perfect solution, and has several obvious restrictions, but might help:

drop table test;
create table test (id number, v1 varchar2(10));

insert into test values (1,'A');
insert into test values (1,'C');
insert into test values (3,'B');
insert into test values (1,'D');

commit;

create or replace function my_concat(i_id number) return varchar2 as m_concat varchar2(4000);
begin
 if i_id is null then
  for r1 in (select v1 from test) loop
   m_concat := m_concat || r1.v1;
  end loop;
 else
  for r1 in (select v1 from test where id = i_id) loop    m_concat := m_concat || r1.v1;
  end loop;
 end if;
 return m_concat;
end;
/

select id, my_concat(id)
from test
group by id;

select my_concat(null)
from test
group by null
;

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

fumi wrote in message <7ua91c$379$5_at_news.seed.net.tw>...
>
><bruce.reeves_at_srs.gov> wrote in message news:7u7bu3$i15$1_at_nnrp1.deja.com...
>> I need help writing a single-group group function. This function
>> needs to concat the content of a field for all rows for a given
>
>
>No. You can not write a user-defined group function.
>
>
>
Received on Sun Oct 17 1999 - 04:29:11 CDT