SQL. Pivot.(Draft)

Воспользуемся известной схемой SCOTT, для примера преобразуем таблицу emp. А именно, создадим новую табличку, в которой добавим несколько статей доходов работникам по некой логике. Это тривиально, по коду всё видно:

--Извиняюсь за заглавные буквы - DBeaver сам их такими делает и я пока не 
--искал, как это побороть. Всё-таки не основной мой рабочий инструмент.
CREATE TABLE scott.emp_2_pivot as(
SELECT empno,ename,job,deptno,hiredate
, x.sal_item
, CASE 
	WHEN sal_item = 'Оклад' THEN sal 
    WHEN sal_item = 'Надбавка за выслугу лет' AND EXTRACT (YEAR FROM hiredate) = 1980 THEN sal * k
    WHEN sal_item = 'Надбавка за президентство' AND JOB = 'PRESIDENT' THEN sal* k
    WHEN sal_item = 'Надбавка за менеджерство' AND JOB = 'MANAGER' THEN sal* k
    WHEN sal_item = 'Надбавка за продажность' AND JOB = 'SALESMAN' THEN sal* k
    WHEN sal_item = 'Премия ударнику социалистического труда' AND JOB = 'CLERK' THEN sal* k
  END sal_item_val
FROM scott.emp e  
cross join -- ниже новые статьи доходов для работников
(select 'Надбавка за выслугу лет' sal_item,  0.1 k from dual
union all 
select 'Премия ударнику социалистического труда' sal_item, 0.2 k from dual
union all
select 'Надбавка за менеджерство' sal_item , 0.15 k from dual
union all
select 'Надбавка за продажность'  sal_item, 0.1 k from dual
union all
select 'Надбавка за президентство' sal_item, 1.2 k from dual
union all
select 'Оклад' sal_item, 1 k from dual
) x
)

После нашего апгрейда зарплаты видим, что у работника ADAMS появилась строка с суммой за ударный труд, ALLEN стал богаче на $160 как продажник, а BLAKE к окладу дополнительно получил $427.5 как менеджер.

Следующий шаг - собственно использование операции PIVOT. Развернём наши строки с денежными суммами в столбцы и дадим им осмысленные названия, используя алиасы.

select * from (
select empno,  ename, job, deptno, hiredate, sal_item, sal_item_val
from scott.emp_2_pivot)
pivot(
sum(sal_item_val) 
for sal_item in ('Надбавка за выслугу лет' AS year_bonus,
                 'Премия ударнику социалистического труда' social_bonus
                 , 'Надбавка за менеджерство' manag_bonus
                 , 'Надбавка за президентство' prez_bonus
                 , 'Оклад' salary) --используем алиасы - это будут названия столбцов
)

Посмотрим, как оно выглядит.

Стоит знать, что если в исходной таблице не будет каких-то значений из списка в блоке pivot - например, я возьму и удалю все строки со статьёй 'Надбавка за президентство', - эта колонка (PREZ_BONUS) в селекте будет присутствовать, но исключительно со значениями NULL.

Кстати, можно использовать более одной агрегатной функции в PIVOT.

Возможно, у кого-то возникнет вопрос: а зачем это может понадобиться, ведь функция пивотных таблиц есть, наверное, в любом репортинге?!

Ответ прост: нам нужно из таблицы, подобной scott.emp_2_pivot вывести какие-то колонки в запросе - реальная задача.

--видели такое? я не только видел, но ещё и писал 8-0

select t.*
, pt.sal_item_val as year_bonus
, pt2.sal_item_val social_bonus
--и еще много полей
from scott.emp t
left
join scott.emp_2_pivot pt on t.empno = pt.empno and pt.sal_item = 'Надбавка за выслугу лет'
left
join scott.emp_2_pivot pt2 on t.empno = pt2.empno and pt2.sal_item = 'Премия ударнику социалистического труда'
-- и ещё много джойнов с этой таблицей

P.S. Нужно будет дописать, есть ли какие-то принципиальные отличия в конструкции PIVOT у MS SQL Server от оракельных.