HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
String concatenation as aggregation function
ORACLE STUFFS
18-Aug-2008
Create your own aggregation function. The sample returns all the column text as a concatenation of them.

Select Name from emp where dept=13;

NAME ----- John Smith Peter Cohn Jeniffer Horten

Select STRAGG(Name) from emp where dept=13;

STRAGG(NAME) ----- John Smith,Peter Cohn,Jeniffer Horten

Script: stragg.sql

create or replace type string_agg_type as object
 (total varchar2(4000),
  delimiter Varchar2(4000),
  static function ODCIAggregateInitialize
        (sctx IN OUT string_agg_type )  return number,
  member function ODCIAggregateIterate
        (self IN OUT string_agg_type
        ,p_value IN varchar2)           return number,
  member function ODCIAggregateTerminate
        (self IN string_agg_type,

...more

Anyway, following XML based solution is much easier going. It does not require any types installed and it does the same work.

select 
 rtrim(xmlagg(xmlelement(c,name||',')).extract('//text()'),',') csv
from emp where dept=13;

Ludek Bob Jankovsky
All Right Reserved © 2007, Designed by Bob Jankovsky