Concatenar valores de una columna en 11g R2 (usando la función LISTAGG)

Varias veces me han preguntado como hacer para concatenar los valores de una columna en una sola fila agrupados por otra cierta columna. Para eso se necesita un operador de concatenación tal como existe el operador SUM() o el AVG() para sumar o sacar el promedio de un conjunto de columnas con cierto criterio de agrupamiento. Para solucionar esto lo que siempre sugeria era crear el operador STRAGG, que es una función que pueden encontrar en la pagina asktom.oracle.com. Abajo voy a crear la función para mostrarles como funciona:

rop@ROP92> create or replace type string_agg_type as object
2  (
3     total varchar2(4000),
4
5     static function
6          ODCIAggregateInitialize(sctx IN OUT string_agg_type )
7          return number,
8
9     member function
10          ODCIAggregateIterate(self IN OUT string_agg_type ,
11                               value IN varchar2 )
12          return number,
13
14     member function
15          ODCIAggregateTerminate(self IN string_agg_type,
16                                 returnValue OUT  varchar2,
17                                 flags IN number)
18          return number,
19
20     member function
21          ODCIAggregateMerge(self IN OUT string_agg_type,
22                             ctx2 IN string_agg_type)
23          return number
24  );
25  /

Type created.


rop@ROP92> create or replace type body string_agg_type
2  is
3
4  static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
5  return number
6  is
7  begin
8      sctx := string_agg_type( null );
9      return ODCIConst.Success;
10  end;
11
12  member function ODCIAggregateIterate(self IN OUT string_agg_type,
13                                       value IN varchar2 )
14  return number
15  is
16  begin
17      self.total := self.total || ',' || value;
18      return ODCIConst.Success;
19  end;
20
21  member function ODCIAggregateTerminate(self IN string_agg_type,
22                                         returnValue OUT varchar2,
23                                         flags IN number)
24  return number
25  is
26  begin
27      returnValue := ltrim(self.total,',');
28      return ODCIConst.Success;
29  end;
30
31  member function ODCIAggregateMerge(self IN OUT string_agg_type,
32                                     ctx2 IN string_agg_type)
33  return number
34  is
35  begin
36      self.total := self.total || ctx2.total;
37      return ODCIConst.Success;
38  end;
39  end;
40  /

Type body created.

rop@ROP92>
rop@ROP92> CREATE or replace
2  FUNCTION stragg(input varchar2 )
3  RETURN varchar2
4  PARALLEL_ENABLE AGGREGATE USING string_agg_type;
5  /

Function created.

rop@ROP92>
rop@ROP92> select deptno, stragg(ename)
2    from scott.emp
3   group by deptno
4  /

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

En el ejemplo concatené los empleados en una sola fila agrupados por el departamento al cual pertenecen. Si tuvieran que concatenar mas valores y no le alcanza con varchar2 tambien pueden encontrar en el sitio de Tom una variante para usar CLOB, aunque es bastante mas lenta.

Ahora bien, en 11g R2 hubo una actualizacion importante de la funciones analiticas, tambien llamadas Analytic Functions II, y una de estas nuevas funciones (LISTAGG)se puede utilizar para hacer lo mismo que stragg en forma nativa evitando tener que crear el tipo stragg y demás. Les muestro un ejemplito para listar los tipos de trabajo que hay en cada departamento:

rop@ROP112> select deptno, listagg(job,',') within group (order by job) jobs
2    from (select distinct deptno, job from scott.emp)
3   group by deptno
4   order by deptno
5  /

DEPTNO JOBS
---------- ------------------------------
   10 CLERK,MANAGER,PRESIDENT
   20 ANALYST,CLERK,MANAGER
   30 CLERK,MANAGER,SALESMAN

Como siempre digo, es importante leer los manuales cada vez que se libera una nueva versión, sobre todo el "Oracle New Features" que es un resumen de las nuevas caracteriticas. Me sucede a menudo que veo codigos o formas de administración antiguas que insumen muchas horas para hacer lo mismo que ya esta resuelto en forma nativa o mas simple a partir de una nueva versión.