Полезные Скрипты Oracle

Select e.job_id , e.department_id , SUM(e.salary) sum_s
From hr.employees e
Group by e.job_id , e.department_id
Order by e.job_id , e.department_id;

Результат:

JOB_ID	DEPARTMENT_ID	SUM_S
AC_ACCOUNT	110	8300
AC_MGR	110	12008
AD_ASST	10	4400
AD_PRES	90	24000
AD_VP	90	34000
FI_ACCOUNT	100	39600
FI_MGR	100	12008
HR_REP	40	6500
IT_PROG	60	28800
MK_MAN	20	13000
MK_REP	20	6000
PR_REP	70	10000
PU_CLERK	30	13900
PU_MAN	30	11000
SA_MAN	80	61000
SA_REP	80	243500
SA_REP		7000
SH_CLERK	50	64300
ST_CLERK	50	55700
ST_MAN	50	36400

А теперь используем отметку PIVOT,чтобы получить суммарную зарплату каждой job_id для departent_id (10,20,30,40):

SELECT * FROM
(SELECT e.department_id , e.job_id ,e.salary FROM hr.employees e) pivot_data
PIVOT ( SUM(salary) FOR department_id IN (10,20,30,40));

Или

WITH
pivot_data as (SELECT e.department_id , e.job_id ,e.salary FROM   hr.employees e)

  SELECT * FROM pivot_data
  PIVOT ( SUM(salary) FOR department_id IN (10,20,30,40));

Результат:

JOB_ID	10	20	30	40
IT_PROG
AC_MGR
AC_ACCOUNT
ST_MAN
PU_MAN			11000
AD_ASST	4400
AD_VP
SH_CLERK
FI_ACCOUNT
FI_MGR
PU_CLERK			13900
SA_MAN
MK_MAN		13000
PR_REP
AD_PRES
SA_REP
MK_REP		6000
ST_CLERK
HR_REP				6500

Тот же результат без отметки PIVOT :

SELECT e.job_id,

(SELECT sum(d1.salary) FROM hr.employees d1 WHERE d1.department_id =10 AND d1.job_id=e.job_id
GROUP BY d1.department_id) "10",

(SELECT sum(d2.salary) FROM hr.employees d2 WHERE d2.department_id =20 AND d2.job_id=e.job_id
GROUP BY d2.department_id) "20",

(SELECT sum(d3.salary) FROM hr.employees d3 WHERE d3.department_id =30 AND d3.job_id=e.job_id
GROUP BY d3.department_id) "30",

(SELECT sum(d4.salary) FROM hr.employees d4 WHERE d4.department_id =40 AND d4.job_id=e.job_id
GROUP BY d4.department_id) "40"

FROM hr.employees e GROUP BY e.job_id

Использование PIVOT отметка в SQL (Oracle 11g)