User-defined Aggregate Function in Oracle

Author    Book                     Author    Books
--------+--------                     --------+--------
poly    |   A                          poly   |   A,B,C
poly    |   B                          amy    |   D,E
poly    |   C         ====>
amy     |   D
amy     |   E

 

HOW?

select author, to_list(book) as books
  from table_name
group by author;

 

SO SIMPLE? 

Yes, we just need to create a user-defined aggregate function as follows:

 

1. create type object

create or replace 
type tolist as object 
( 
  list varchar2(2000), 

  static function ODCIAggregateInitialize 
  ( actx in out tolist 
  ) return number, 

  member function ODCIAggregateIterate 
  ( self  in out tolist , 
    value in varchar2 
  ) return number, 

  member function ODCIAggregateTerminate 
  ( self in tolist , 
    returnValue  out varchar2, 
    flags in varchar2 
  ) return number, 

  member function ODCIAggregateMerge 
  (self in out tolist , 
   ctx2 in     tolist  
  ) return number 
) 

 

 2. create type body

create or replace 
type body tolist is 

static function ODCIAggregateInitialize 
  ( actx in out tolist
  ) return number is 
  begin
    actx := tolist('');
    return ODCIConst.Success; 
  end; 


member function ODCIAggregateIterate 
  ( self  in out tolist, 
    value in varchar2
  ) return number is 
  begin 
        if self.list is null then self.list := value;
        else
           self.list := self.list || ',' || value; 
        end if;     
     return ODCIConst.Success; 
  end; 


 member function ODCIAggregateTerminate 
  ( self in tolist, 
    returnValue out varchar2, 
    flags in varchar2 
  ) return number is 
  begin 
   returnValue:= self.list; 
   return ODCIConst.Success; 
  end; 


  member function ODCIAggregateMerge 
  (self in out tolist, 
   ctx2 in     tolist 
  ) return number is 
  begin 
    if ctx2.list <> '' then 
       self.list := self.list || ',' || ctx2.list; 
    end if; 
    return ODCIConst.Success; 
  end; 

end; 

 

3. create function

CREATE OR REPLACE FUNCTION to_list
( x varchar2
) RETURN varchar2
PARALLEL_ENABLE
AGGREGATE USING tolist;

 

done

 

---------------------------------------------

http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96595/dci11agg.htm#1005029

http://www.oracle.com/technology/oramag/oracle/06-jul/o46sql.html

 

 

==========================

Supplement

 

"Displaying multiple records in one row" give a summary :

1. SELECT wmsys.wm_concat(dname) departments FROM dept;  (10g)

2. A way to get around the restriction that user-defined aggregates may only have one argument, which allows you to specify an alternative separator character.

3. A really delicate way using just sql

SELECT deptno
     , LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
FROM   ( SELECT deptno
              , ename
              , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr
              , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
         FROM   emp )
GROUP BY deptno
CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
START WITH curr = 1;

4. Another approach involves harnessing the dark power of XML

SELECT deptno
     , RTRIM
       ( xmlagg (xmlelement (c, ename || ',') order by ename).extract ('//text()')
       , ',' ) AS concatenated
FROM   emp
GROUP BY deptno;