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:

LISTA_NOMBRES PUESTO NOMBRE
Amelia; Elena; Jacobo; Luis; Mercedes Puesto 1 Amelia
Amelia; Elena; Jacobo; Luis; Mercedes Puesto 1 Elena
Amelia; Elena; Jacobo; Luis; Mercedes Puesto 1 Jacobo
Amelia; Elena; Jacobo; Luis; Mercedes Puesto 1 Luis
Amelia; Elena; Jacobo; Luis; Mercedes Puesto 1 Mercedes
Carlos; Carmen; Luisa; Pepe Puesto 2 Carlos
Carlos; Carmen; Luisa; Pepe Puesto 2 Carmen
Carlos; Carmen; Luisa; Pepe Puesto 2 Luisa
Carlos; Carmen; Luisa; Pepe Puesto 2 Pepe
Jose Puesto 3 Jose

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:

LISTA_NOMBRES SUMA_CANTIDAD CUENTA_CANTIDAD
Amelia 1; Elena 2; Jacobo ; Luis 1; Mercedes 2
6
5
Carlos 1; Carmen 1; Luisa ; Pepe 1
3
4
Jose 3
3
1

Comments are closed.