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
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.
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment