Re: Group functions

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 22 Jul 1999 12:28:14 GMT
Message-ID: <379a0c12.82468853@inet16.us.oracle.com>

On Thu, 22 Jul 1999 07:48:02 -0400, "Mike Heisz" <mheisz_at_rim.net> wrote:

>I should clarify what I meant:
>I want to write my own group function in pl/sql.
>Specifcally I wan to write a group concatenation function that will take the
>values in a query and concatenate them. The SQL would be something like:
>
>select column1,CONCAT_GROUP(column2)
>from ......
>group by column1;
>
>PL/SQL:
>function CONCAT_GROUP IS
>BEGIN
> -- how do I do this part?
>END;
>
>Mike

If I understand you correctly, you want to do something like...

given:

SQL> select * from test;

         N V
---------- ----------

         1 A
         1 B
         1 C
         2 A
         2 B
         3 X
         3 Y
         3 Z

You want the result to look like

N V_CONCAT
- --------
1 A B C
2 A B
3 X Y Z

If that is right then the function CONCAT_GROUP could look like

SQL> l
  1 create or replace
  2 function concat_group( p_id number ) return varchar2 as   3 l_str long := null;
  4 l_sep varchar2(1) := null;
  5 begin
  6 for c in ( select v from test where n = p_id ) loop

  7      l_str := l_str || l_sep || c.v;
  8      l_sep := ' ';

  9 end loop;
 10 return l_str;
 11* end concat_group;
SQL> /
Function created.

SQL> l
  1 select n, concat_group(n) v_concat   2 from test
  3* group by n
SQL> /  N V_CONCAT
-- ----------
 1 A B C
 2 A B
 3 X Y Z

hope this helps.

chris.

>Breno de Avellar Gomes <brenogomes_at_ieee.org> wrote in message
>news:37966D70.DE8C7D7A_at_ieee.org...
>> Try this:
>>
>> SELECT AVG(cost), MAX(price) FROM products;
>>
>> Feel free to leave a note for more details.
>>
>> Regards
>>
>> Breno
>>
>>
>> Mike Heisz wrote:
>>
>> > Does anyone know how to write (if posssible?) a function to be used in a
>> > group function in SQL (i.e. something similar to avg)? I found mention
>of
>> > this possibility in the Oracle docs but there are no examples of how to
>do
>> > it.
>> >
>> > Mike
>>
>

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jul 22 1999 - 07:28:14 CDT