Oracle 11gR2 LISTAGG Analytical Function – Concatenate / Select Multiple Rows of a Table to Single

advertisements

_____________________________________________________________________________________________________________________

This function act as a single set aggregate as well as group set aggregate function. In single set function it returns all rows into a single output row and in group set aggregate function it groups the records as defined in the group by function. As a analytic function it partitions the query result set into groups based on one or more conditions in the query_partition_clause.

LISTAGG Function Syntax:

LISTAGG (measure_expression [, 'delimiter_expression'])

WITHIN GROUP (order_by_clause) [OVER query_partition_clause]

Where measure_expression can be any expression and null values in this are ignored, delimiter_expression designates the strings that is to separate the measure values. This is optional and defaults to NULL.

The Order_by_calause determines the order in which the concatenated values are returned.

Examples:

I have a table emp with following content.

SQL> select * from emp;

EMP_NAME DEPT SAL

------------------------------ ---------- ----------

Rupal 10 5000

Hero 10 5500

Jain 10 4000

John 20 6000

Riju 20 7000

Sam 20 6500

Kiran 30 5500

Peter 30 6800

King 30 7600

Roshan 30 5500

Roshan 10 5500

David 10

12 rows selected.

In below example, LISTAGG function act as a single_set aggregate function.

SQL> select listagg(emp_name, ', ') within group (order by dept) emplist from emp;

EMPLIST

--------------------------------------------------

David, Hero, Jain, Roshan, Rupal, John, Riju, Sam,

King, Kiran, Peter, Roshan

In below example, LISTAGG function act as a group_set aggregate function.

SQL> select dept , listagg(emp_name, ', ') within group (order by dept) emplist from emp group by dept;

DEPT EMPLIST

---------- --------------------------------------------------

10 David, Hero, Jain, Roshan, Rupal

20 John, Riju, Sam

30 King, Kiran, Peter, Roshan

In below example, LISTAGG function acts as an analytic function. It select dept, employee name, list of all employees in the corresponding department.

SQL> SELECT dept, emp_name, LISTAGG(emp_name, ',')

WITHIN GROUP (ORDER BY SAL)

OVER (PARTITION BY dept) AS employee_list

FROM emp;

DEPT EMP_NAME EMPLOYEE_LIST

---------- ------------------------------ -----------------------------

10 Jain Jain,Rupal,Hero,Roshan,David

10 Rupal Jain,Rupal,Hero,Roshan,David

10 Hero Jain,Rupal,Hero,Roshan,David

10 Roshan Jain,Rupal,Hero,Roshan,David

10 David Jain,Rupal,Hero,Roshan,David

20 John John,Sam,Riju