Función LISTAGG Oracle

 Objetivo:

• Conocer la función LISTAGG.
• Ver algunos ejemplos útiles.
____________________________________________________________

Descripción
La función agregada de Oracle LISTAGG opera sobre una lista de registros y retorna en una sola línea todos los valores separados por un delimitador especificado. En esencia es usada para denormalizar datos de varios registros en un registro único. 

Sintaxis

LISTAGG(measure_expr [, 'delimiter'] ON OVERFLOW ERROR)

  WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

En la Sintaxis:

1. measure_expr es la columna o expresión que se desea concatenar.

Tener en cuenta que los valores NULOS son ignorados.

2. delimiter único es el carácter separador. Este parámetro es opcional, 

3. order_by_clause establece el orden que se mostrará la lista de valores.

4. query_partition_clause devuelve todas las filas y duplica el resultado 

de LISTAGG para cada fila de la partición.

Ejemplos
A continuación algunas consultas que muestran el uso de la función LISTAGG, para ellos usamos las tablas del esquema HR.

SELECT
        e.department_id             AS  departamento
        ,    COUNT(e.first_name)    AS  cantidad_empleados
        , LISTAGG(
                    e.first_name,
                    ', '
                ) WITHIN GROUP( ORDER BY e.first_name)                   AS  empleados_x_dept
FROM    hr.employees    e
GROUP BY e.department_id
ORDER BY cantidad_empleados  DESC;

/*La consulta retorna el listado de todos los empleados agrupados por el departamento al que pertenecen*/

La Cláusula OVERFLOW

La cláusula ON OVERFLOW permite controlar la ocurrencia de la excepción ORA-01489 cuando la cadena retornada supera el limite máximo del tipo de datos retornado. Con esta evitamos el error y en su lugar podemos truncar la cadena retornada.

SELECT
        empleo
        , COUNT(1)  AS  cantidad
        , LISTAGG(detalle_empleado, ';' ON OVERFLOW TRUNCATE '**')
            WITHIN GROUP (ORDER BY empleo)   AS detalle_empleado
FROM
(
SELECT
        j.job_title     AS  empleo,
        '|Código: '          ||e.employee_id||'|'||
        'Nombre: '          ||e.first_name||' '||e.last_name||'|'||
        'Correo: '          ||e.email||'|'||
        'Telefono: '        ||e.phone_number||'|'||
        'Salario: '         ||e.salary||'|'||
        'Departamento: '    ||d.department_name||'|'||
        'Dirección: '       ||l.street_address||', '||l.city||', '||
        l.state_province||', '||l.postal_code||', '||c.country_name
        AS  detalle_empleado

FROM
        hr.employees e
        LEFT    JOIN    hr.departments d
            ON  (d.department_id    =   e.department_id)
        LEFT    JOIN    hr.jobs j
            ON  (j.job_id    =   e.job_id)
        LEFT    JOIN    hr.locations l
            ON  (l.location_id    =   d.location_id)
        LEFT    JOIN    hr.countries c
            ON  (c.country_id   =   l.country_id)
)
GROUP  BY   empleo
ORDER BY    cantidad DESC
;

/*En la anterior consulta unimos distintas tablas para traer las informaciones relativas a los empleados; Notar el uso de la cláusula ON OVERFLOW TRUNCATE '**'; de omitirla se generaría la mencionada excepcion ORA-01489*/