Monday, June 16, 2008

Oracle analog of MySQL GROUP_CONCAT function

I was looking for Oracle analog of MySQL GROUP_CONCAT function. There is a solution on Ask Tom site. So here it is:


create or replace type string_agg_type as object (
  total varchar2(4000),

  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type )
  return number,

  member function ODCIAggregateIterate(self IN OUT string_agg_type, value IN varchar2 )
  return number,

  member function ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT varchar2, flags IN number)
  return number,

  member function ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
  return number
);
/

create or replace type body string_agg_type is
  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
  return number
  is
  begin
    sctx := string_agg_type( null );
    return ODCIConst.Success;
  end;

  member function ODCIAggregateIterate(self IN OUT string_agg_type, value IN varchar2 )
  return number
  is
  begin
    self.total := self.total || ',' || value;
    return ODCIConst.Success;
  end;

  member function ODCIAggregateTerminate(self IN string_agg_type, returnValue OUT varchar2, flags IN number)
  return number
  is
  begin
    returnValue := ltrim(self.total,',');
    return ODCIConst.Success;
  end;

  member function ODCIAggregateMerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
  return number
  is
  begin
    self.total := self.total || ctx2.total;
    return ODCIConst.Success;
  end;
end;
/

create or replace function stragg(input varchar2)
return varchar2
parallel_enable aggregate using string_agg_type;
/

After this you can use this function like this:
select t1.id, stragg(t2.name) as names from table1 t1, table2 t2 where t1.id=t2.id group by t1.id;

It will produce comma separated list of the names - something like this:
ID NAMES
-- ------------------------------------
10 CLARK,KING,MILLER
20 SMITH,FORD,ADAMS,SCOTT,JONES
30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

No comments: