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

5

 

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

 

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

5 Comments

  1. Hi Lucas,

    That was good examples and feature. But how can you implement below same thing in 10g using collect or any other method

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

  2. The possibility to use the collect function in combination unique or distinct or order by is new Oracle 11gr1 . I believe it isn’t possible in Oracle 10.

    I hoped that listagg would return a clob instead of a varchar2. What happens when the 4000 limit is surpassed? I can’t test it because I don’t have Linux so I can’t install 11gr2 yet.

  3. Oops, in that case I have overlooked that particular feature in the COLLECT operator. And I stand corrected. That is the risk you run with such a sophisticated audience! Will we meet at OOW Laurent? Thanks for your comment! Best regards, Lucas

  4. There are some limitations on using this [COLLECT] operator: it does not support an order by especially for the collected values

    Really?

    select collect(ename order by ename) from emp

    seems to be a valid syntax