LISTLAG
LISTAGG function Syntax
Aggregate Syntax: LISTAGG(measure_expr [, ‘delimiter‘]) WITHIN GROUP (order_by_clause)
Analytic Syntax : LISTAGG(measure_expr [, ‘delimiter‘]) WITHIN GROUP (order_by_clause)[OVER query_partition_clause]
For a specified measure, LISTAGG orders data within each group specified
in the ORDER BY clause and then concatenates the values of the measure column.
SELECT DEPARTMENT_ID, LISTAGG(FIRST_NAME, ‘|‘) WITHIN GROUP (ORDER BY FIRST_NAME) "Emp_list" FROM EMPLOYEES group by DEPARTMENT_ID
PIVOT
select * from ( select department_id, job_id from employees ) pivot ( count(1) for job_id in (‘MK_MAN‘,‘MK_REP‘,‘PU_CLERK‘,‘PU_MAN‘) ) ORDER BY 1
select * from ( select department_id,job_id, salary from employees ) pivot ( SUM(salary) for job_id in (‘MK_MAN‘,‘MK_REP‘,‘PU_CLERK‘,‘PU_MAN‘) ) ORDER BY 1