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!



Post new comment

The content of this field is kept private and will not be shown publicly.

More information about formatting options

By submitting this form, you accept the Mollom privacy policy.