User-defined Aggregate Function in Oracle

wybpdpdmgu

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;