« Chequeo y configuración de Oracle EM Express 12c (Enterprise Manager Express)Oracle Golden Gate : Dejando el manager como servicio Windows »

Función LISTAGG en 11gr2 : Para concatenar valores en una sola columna



El típico problema al que todos nos hemos enfrentado, sacamos un listado de valores, pero lo que necesitamos es concatenarlos hacia el lado , en una sola fila ..


Obs : Se nota como piensa la señorita....¿cierto?

Por ejemplo :

select ciudad from pais where nombre='Chile';

CIUDAD
------
Arica
La Serena
Valparaíso
Santiago

Pero lo que queremos es la siguiente salida

CIUDAD
------
Arica;La Serena;Valparaíso;Santiago

Para resultados pequeños , esto es muy fácil, pero ¿si fueran miles? :'(

Esto lo resolvemos quizás con un Pl/SQL , con un cursor que concatene los valores en un string mayor, llevando los valores a una planilla Excel y concatenando, etc, etc, etc.

Pues bien, esto a partir de 11gr2 es sumamente sencillo, con una función muy poderosa llamada LISTAGG.

He acá unos pequeños ejemplos :

- Tomamos un listado de los datafiles disponibles

SQL> select file_name from dba_data_files;

FILE_NAME
-----------------------------------------------
C:\APP\LIGARIUS\ORADATA\PROD12C\SYSTEM01.DBF
C:\APP\LIGARIUS\ORADATA\PROD12C\SYSAUX01.DBF
C:\APP\LIGARIUS\ORADATA\PROD12C\UNDOTBS01.DBF
C:\APP\LIGARIUS\ORADATA\PROD12C\USERS01.DBF
C:\APP\LIGARIUS\ORADATA\PROD12C\USERS02.DBF
C:\APP\LIGARIUS\ORADATA\PROD12C\SYSTEM02.DBF


Como podemos apreciar , los datos aparecen listados de forma normal.


Para que aparezcan hacía la derecha, concatenados por algún carácter se ejecuta una función analítica llamada LISTAGG

1 select listagg(file_name,';')
2 within group (order by file_id)
3* from dba_data_files

LISTAGG(FILE_NAME,';')WITHINGROUP(ORDERBYFILE_ID)
----------------------------------------------------------------------------------------------------
C:\APP\LIGARIUS\ORADATA\PROD12C\SYSTEM01.DBF;C:\APP\LIGARIUS\ORADATA\PROD12C\USERS02.DBF;C:\APP\LIGA
RIUS\ORADATA\PROD12C\SYSAUX01.DBF;C:\APP\LIGARIUS\ORADATA\PROD12C\SYSTEM02.DBF;C:\APP\LIGARIUS\ORADA
TA\PROD12C\UNDOTBS01.DBF;C:\APP\LIGARIUS\ORADATA\PROD12C\USERS01.DBF


listagg(file_name,';') : Le indicamos que columna queremos concatenar y el carácter de concatenación.
within group (order by file_id) : En esta claúsula le indicamos como ordenar las salidas a concatenar

En este ejemplo concatenamos los datafiles de cada uno de los tablespaces existentes

1 select tablespace_name , listagg(file_name,';')
2 within group (order by file_id) datos
3 from dba_data_files
4 group by tablespace_name
5* order by tablespace_name

TABLESPACE_NAME DATOS
--------------- -----------------------------------------------------------------------------------------
SYSAUX C:\APP\LIGARIUS\ORADATA\PROD12C\SYSAUX01.DBF
SYSTEM C:\APP\LIGARIUS\ORADATA\PROD12C\SYSTEM01.DBF;C:\APP\LIGARIUS\ORADATA\PROD12C\SYSTEM02.DBF
UNDOTBS1 C:\APP\LIGARIUS\ORADATA\PROD12C\UNDOTBS01.DBF
USERS C:\APP\LIGARIUS\ORADATA\PROD12C\USERS02.DBF;C:\APP\LIGARIUS\ORADATA\PROD12C\USERS01.DBF


Una excelente función :>>

La documentación oficial
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions089.htm#SQLRF30030


by Ligarius
14.08.14. 20:11:11. 466 words, 6193 views. Categories: Base de datos ,