Función Oracle: LISTAGG

La función LISTAGG se utiliza, para pivotar filas en una única columna concatenando los valores con un separador indicado. LA diferencia con la función PIVOT, es que pivota un valor a una columna sin combinarlas en el mismo campo (ver artículo PIVOT).

Como alternativas en versiones anteriores podía utilizar CASE, pero no era dinámico, o la función COLLECT, pero es menos eficiente y más compleja de utlizar.

A partir de la versión 11.2, Oracle introdujo la función LISTAGG.

Sintaxis:

LISTAGG(campo [,'delimitador']) WITHIN GROUP (sentencia_de_ordenacion) [OVER ([PARTITION BY  [,, ...]])]

campo: campo a concatenar. Los valores nulos son ignorados.
delimitador: separador de los valores concatenados
sentencia_de_ordenacion: sentencia de ordenación dentro de la lista
expresion_valor_1, expresion_valor_2, …: Puede ser una columna u otra función no analítica a obtener, para particionar el resultado obtenido

EJEMPLO 1: Obtiene registro para cada nombre y listado de nombres con el mismo puesto

CREATE TABLE tabla
(nombre VARCHAR2(100),
 puesto VARCHAR2(100),
 cantidad NUMBER);

INSERT INTO tabla (cantidad, nombre, puesto) VALUES (1,    'Luis',     'Puesto 1');
INSERT INTO tabla (cantidad, nombre, puesto) VALUES (1,    'Carlos',   'Puesto 2');
INSERT INTO tabla (cantidad, nombre, puesto) VALUES (2,    'Elena',    'Puesto 1');
INSERT INTO tabla (cantidad, nombre, puesto) VALUES (NULL, 'Luisa',    'Puesto 2');
INSERT INTO tabla (cantidad, nombre, puesto) VALUES (NULL, 'Jacobo',   'Puesto 1');
INSERT INTO tabla (cantidad, nombre, puesto) VALUES (1,    'Carmen',   'Puesto 2');
INSERT INTO tabla (cantidad, nombre, puesto) VALUES (2,    'Mercedes', 'Puesto 1');
INSERT INTO tabla (cantidad, nombre, puesto) VALUES (1,    'Pepe',     'Puesto 2');
INSERT INTO tabla (cantidad, nombre, puesto) VALUES (1,    'Amelia',   'Puesto 1');
INSERT INTO tabla (cantidad, nombre, puesto) VALUES (3,    'Jose',     'Puesto 3');

commit;

SELECT LISTAGG(nombre, '; ') WITHIN GROUP (ORDER BY nombre) over (partition by puesto) LISTA_NOMBRES,
       puesto,
       nombre
FROM tabla t
ORDER BY puesto, nombre;

Obtendríamos el siguiente resultado:

EJEMPLO 2: Obtener la lista de nombres, para un puesto y sumando las cantidades, para un puesto determinado

SELECT LISTAGG(nombre||' '||cantidad, '; ') WITHIN GROUP (ORDER BY nombre) LISTA_NOMBRES,
       SUM(t.cantidad) SUMA_CANTIDAD,
       COUNT(1) CUENTA
FROM tabla t
group by puesto
ORDER BY 2 desc,3 desc;

Obtendríamos el siguiente resultado: