LISTAGG – LISTAGG (measure_expr [, ‘delimiter_expr’]) WITHIN GROUP (ORDER
BY clause) [OVER PARTITION BYclause]
BY clause) [OVER PARTITION BYclause]
Scenario:
consider following table.We need to find aggregate results dept wise in form of rows.
Employee table:
| Employee_num | Employee_name | Department ID | Salary |
| 1 | sekhar | 100 | 100000 |
| 2 | Vidya | 100 | 200000 |
| 3 | Nilesh | 101 | 300000 |
Query used:
select Department_ID,listagg(Employee_name,’,’) within group(order by Employee_name) as Employee_name from Employee group by Department_id;
Output:
| Department ID | Employee_name |
| 100 | sekhar,vidya |
| 101 | Nilesh |
