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 collect function in 10g – on Oracle-developer.net
- Tag Cloud analysis in SQL and PL/SQL – Using Collections, Table Operator, Multiset and Collect operator
- Oracle RDBMS 11gR2 – goodbye Connect By or: the end of hierarchical querying as we know it
- SQL*Plus or Report style Break Groups in SQL Query
- Dynamic and Conditional grouping in SQL queries – for flexible results from single query (oh and a useful case for the CUBE operator)
- Yes, it is here too: the PIVOT operator in Oracle 11g
- Oracle SQL – Finding free adjacent seats in an airplane, using Subquery Factoring, Analytical Functions (LISTAGG and LEAD), Outer Join, PIVOT and good old INSTR
- Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring
- Oracle SQL: Using subquery factoring in an INSERT statement
- OTN Yathra 2013 – Spreading the story of Oracle across India – (Half time)
- ADF DVT Speed Date: Present Metrics per Year, Quarter and Month using a zoom-enabled ADF DVT Resource Utilization Gantt and ADF BC
- Using TRUNC in SQL to get the first date in a period
- ADF DVT: Visualizing valid periods using Project and Scheduling Gantt Charts
- Advanced SQL to find valid periods – juggling with outer joins, running totals and analytical functions
- The Very Very Latest in Database Development – slides from the Expertezed presentation
- AMIS Masterclass Advanced SQL – scherp je SQL skills in een pittige en praktijkgerichte dag – maandag 10 december