oracle row/column

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