Oracle. Función para concatenar resultados de tipo texto en una select

Oracle pone a disposición del usuario funciones de agrupación como las funciones MAX, MIN, AVG, ... Pero si nos fijamos, son todas de tipo numérico. Aún así, disponemos también del GROUP BY, pero no llega a solucionar el problema que vamos a plantear.

Este truco para concatenar resultados en Oracle se hizo con Oracle 10g. Aunque en la actualidad sigue siendo muy útil, no he podido probarlo en versiones recientes.

Problema de partida

El objetivo es resolver este problema:

SQL> select deptno, ename
 2    from emp
 3   group by deptno
 4  /

DEPTNO       ENAME
----------   -----------------------------------------
     10      CLARK
     10      KING
     10      MILLER
     20      SMITH
     20      FORD
     20      ADAMS
     20      SCOTT
     20      JONES
     30      ALLEN
     30      BLAKE
     30      MARTIN
     30      TURNER
     30      JAMES
     30      WARD

y obtener los resultados "agrupados" por el número de departamento usando una función de agrupación de manera que tengamos el resultado de la siguiente manera:

select deptno, str_concat(ename)
  2    from emp
  3   group by deptno
  4  /
    DEPTNO   STR_CONCAT(ENAME)
----------   -----------------------------------------
        10   CLARK,KING,MILLER
        20   SMITH,FORD,ADAMS,SCOTT,JONES
        30   ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Fantástico, ¿verdad?. Bien, pues para poder hacer esto es necesario crear en Oracle los siguientes objetos.

Crear una función nueva llamada STR_CONCAT

La función STR_CONCAT se utiliza de la siguiente forma:

CREATE OR REPLACE FUNCTION str_concat(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/

El "Type"

El "Type"

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; -- Separador
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;

/

El "Type bodies"

El "Type bodies":

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
)
/

¿Cómo probarlo?

Una vez creados, basta con usar la función STR_CONCAT como si de una función MAX se tratara.

Conclusiones

Esta función es bastante óptima. Cualquier intento de implementar una agrupación de columnas de tipo texto por otros medios, realizando otro tipo de sqls, o realizando la concatenación desde código Java o similar, sólo complica la implementación y reduce drásticamente el rendimiento provocando que la sql tarde demasiado en ejecutarse.