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.
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 Oracle 9i y versiones posteriores, en asktom han creado la función a medida string_agg_type
- A partir de Oracle 10.1 se puede utilizar la función WM_CONCAT, pero no está ni soportada ni documentada.
- A partir de ORacle 11.2 tenemos disponible la función LISTAGG.
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
Subido por sergio romero … (no verificado) el 23 Septiembre, 2013 - 16:31
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 CATPd. 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
Genial Sergio, y muy curioso.
Subido por Carlos el 23 Septiembre, 2013 - 22:58
En respuesta a Hola Carlos. No he trabajado por sergio romero … (no verificado)
Genial Sergio, y muy curioso. Quién iba a decir que el equivalente a LISTAGG de SQL Oracle (11.2) iba a ser un FOR XML con PATH (y desordenado)?
Muchas gracias por la doble aportación!
Hola, ¿no se te ha presentado
Subido por Anonimo (no verificado) el 3 Marzo, 2016 - 19:20
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