Listagg: New Oracle 11.2 function to summarize and aggregate strings

 
In a LinkedIn debate about how to update from a select with summarized registers in SQL ServerSergio Romero has shared an interesting tip about new summarize functions on Oracle 11g R2.

Oracle 11g LISTAGG Function

 

This is what he said:

Sum function works with numeric datatypes and non numeric datatypes that can be implicitly converted. For example, this solutions works on a numeric column and on a varchar2 column which contains numeric values (bad design). 

But will not summarize char values. For char values, you must use string aggregation techniques: 

  • Oracle 11.2 implements the LISTAGG function (sweet) 
  • Oracle 10.1 and above MAY include the WM_CONCAT fuction, but is an unsupported and undocumented function. 

Of course you can create your own user defined function 


I have made a little script using SUM and LISTAGG functions: 

-- Summarizing and string aggregation example-- 
-- Using hr schema. Oracle 11.2 release -- 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 

 

Thanks for sharing this useful information, Sergio!