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

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
        20 Sam                            John,Sam,Riju
        20 Riju                           John,Sam,Riju
        30 Roshan                         Roshan,Kiran,Peter,King
        30 Kiran                          Roshan,Kiran,Peter,King
        30 Peter                          Roshan,Kiran,Peter,King
        30 King                           Roshan,Kiran,Peter,King

12 rows selected.

DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google