Oracle RDBMS 11gR2 – LISTAGG – New aggregation operator for creating (comma) delimited strings

In our series on the 11g R2 release of the Oracle RDBMS, AMIS staff discusses various functions, features an facilities that have been added, improved or extended in this latest release. These articles are the fruit of several months of studying this new release and projecting which new features and changes would be the most useful to us in our daily practice. This article introduced the LISTAGG operator, new in 11gR2.

An example usage of LISTAGG:

select deptno
,      avg(sal) avg_sal
,      listagg( ename, ',')
       within group (order by sal)
       enames
from   emp
group
by     deptno

Oracle RDBMS 10g introduced the Collect operator. It could be used to aggregate values on all rows in a group into a collection – a predefined TABLE OF <some type>. This operator can be used in statements like this one:

create or replace type
ename_type as table of varchar2(30)
/

select deptno
,      avg(sal) avg_sal
,      cast
       ( collect(ename)
         as ename_type
       ) enames
from   emp
group
by     deptno
/ 

There are some limitations on using this operator: it does not support an order by especially for the collected values, it requires a TYPE definition (usually TABLE OF VARCHAR2) that has to be created before the Collect can be used and it acts only as a pure  Aggregate Function (typically in conjunction with a group by), it is not available as an Analytical function.

(edited) on second thoughts, I should have rephrased the above. First of all – as Laurent pointed out in his comment, an order by expression apparently is allowed with COLLECT (I never knew that one!) just as DISTINCT and UNIQUE can be used. I also found out that you can use COLLECT without predefining a collection type. If you execute a statement like collect(ename) as emps – without the cast to turn the collect outcome into a well defined collection, the database will generate a type for you, on the fly with a system generated name that is quite worthless, but it will work all the same.

The new LISTAGG is similar to yet different from the COLLECT: LISTAGG creates a delimiter (comma, period, colon, etc.) separated string of values. The LISTAGG takes an optional order by clause that specifies the sequence of the values in the string. It returns a VARCHAR2 – so no custom type needs to be set up. And: LISTAGG is available  as Aggregate (with GROUP BY) and Analytical Function (with PARTITION clause).

A simple example of using LISTAGG:

select deptno
,      avg(sal) avg_sal
<strong>,      listagg( ename, ',') within group (order by sal)</strong>
       enames
from   emp
group
by     deptno
/

The results returned by this query:

Here we used LISTAGG as an aggregate function, together with the GROUP BY that in this case indicates that the concattenated string with ENAME values should create this string per department. Within the string, the enames should be ordered by the salary values of the employees.

The next example is on using the LISTAGG as an analytical function, producing aggregate values in line with the records rather than grouped by into a summary result. So for each record processed, the delimiter separated string is produced, in the same way other analytical functions function. That means for example that we could have a ‘running delimited string’ if we would add an order by to the over() clause without a windowing clause.

Here we are after the list of employees (colleagues) that work in the same department as the employee being processed:

select deptno
,      ename
,      sal
,      listagg( ename, ',')
       within group (order by ename)
       over (partition by deptno)
       colleagues
from   emp
order
by     deptno
,      ename

The results:

Resources

11gR2 SQL Language Reference on Collect

11gR2 SQL Language Reference on LISTAGG

the collect function in 10g – on Oracle-developer.net