Listagg: Nueva función de Oracle 11 para hacer agregaciones sobre campos de tipo cadena

 

A raíz de un debate en LinkedIn sobre cómo hacer en SQL Server un update desde una select con registros agregados, ha surgido un tip muy interesante sobre nuevas funciones de agregación en Oracle 11g R2, concretamente sobre la función LISTAGG, que permite agregar los valores de campos de varios registros en uno.

Oracle 11g LISTAGG Function

 

Sergio Romero ha comentado, en relación con las funciones de agregación, que si quisiéramos utilizar una función de agregación sobre valores de cadena que no se pudieran convertir a numéricos, un campo de tipo VARCHAR2 de Oracle que almacenara nombres, por ejemplo, la función de agregación nos devolvería un error.

Para solucionar este inconveniente, lo que se podría hacer es, en lugar de utilizar la función de agregación para obtener un sólo valor para este campo, concatenar todos los valores de los registros en uno único. Para hacerlo, Sergio ha aportado diferentes soluciones, según la versión de Oracle:

 

Para mostrar el funcionamiento de la función LISTAGG, Sergio ha preparado este ejemplo de sumarización y agregación de cadenas utilizando el esquema 'hr' de ejemplo de la release 11.2 de Oracle.

SELECT job_title, SUM(t2.salary)SALARY_SUM, COUNT(t2.job_id)NUMBER_EMP, LISTAGG(last_name||' '||first_name||' '||salary, '; ') WITHIN GROUP (ORDER BY hire_date, last_name) "EMP_LIST" 
FROM jobs t1, employees t2 
WHERE t1.job_id=t2.job_id 
GROUP BY t1.job_title 
ORDER BY 2 desc 

Gracias a Sergio por compartir esta interesante nueva función!

 

Y ahora la pregunta es ¿en SQL Server existe alguna función parecida?

Hola Carlos.

No he trabajado practicamente nada con transact sql, pero buscando en la documentacion, el equivalente es la clausula FOR XML junto con el modo PATH FOR XML PATH ('')

Esta el la documentacion oficial:

http://technet.microsoft.com/es-es/library/ms178107.aspx

http://technet.microsoft.com/es-es/library/ms189885.aspx

Pero como odio lo criptica que puede ser en ocasiones la documentacion de technet, pues mejor un ejemplo practico de la comunidad:

http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/

SELECT CAT.Name AS [Category], STUFF(( SELECT ',' + SUB.Name AS [text()] -- Add a comma (,) before each value FROM Production.ProductSubcategory SUB WHERE SUB.ProductCategoryID = CAT.ProductCategoryID FOR XML PATH('') -- Select it as XML ), 1, 1, '' ) -- This is done to remove the first character (,) from the result AS [Sub Categories] FROM Production.ProductCategory CAT

Pd. Por lo que veo, de serie esta solucion ordena como le da la gana los campos agregados ¿alfabeticamente? Asi que si tiene que ser de otro modo, tenedlo en cuenta.

Ciao.

Slds, Sergio Romero González

 

Hola,

¿no se te ha presentado el caso de utilizar la funcion para una columna que se repite?

Y obviamente no deseas que sea asi

 

Saludos