Проблема
Вы хотите создать кросс-табличный отчет, чтобы преобразовать строки набора результатов в столбцы.
Вы знакомы с традиционными методами поворота (pivoting), но хотели бы попробовать что-нибудь другое. В частности, вы хотите вернуть следующий набор результатов без использования CASE
выражений или объединений:
DEPT_10 DEPT_20 DEPT_30 DEPT_40
------- ----------- ----------- ----------
3 5 6 0
Решение
Используйте PIVOT
оператор, чтобы создать требуемый набор результатов без CASE
выражений или дополнительных объединений:
1 select [10] as dept_10,
2 [20] as dept_20,
3 [30] as dept_30,
4 [40] as dept_40
5 from (select deptno, empno from emp) driver
6 pivot (
7 count(driver.empno)
8 for driver.deptno in ( [10],[20],[30],[40] )
9 ) as empPivot
Обсуждение
Поначалу PIVOT
оператор может показаться странным, но операция, которую он выполняет в решении, технически аналогична более знакомому запросу транспонирования, показанному здесь:
select sum(case deptno when 10 then 1 else 0 end) as dept_10,
sum(case deptno when 20 then 1 else 0 end) as dept_20,
sum(case deptno when 30 then 1 else 0 end) as dept_30,
sum(case deptno when 40 then 1 else 0 end) as dept_40
from emp
DEPT_10 DEPT_20 DEPT_30 DEPT_40
------- ---------- ---------- ----------
3 5 6 0
Теперь, когда вы знаете, что, по сути, происходит, давайте разберемся, что PIVOT
оператор делает. Строка 5 решения показывает встроенное представление с именем DRIVER
: from (select deptno, empno from emp) driver
Мы использовали псевдоним, DRIVER
потому что строки из этого встроенного представления (или табличного выражения) передаются непосредственно в PIVOT
операцию. PIVOT
Оператор поворачивает строки в столбцы, оценивая элементы , перечисленные в строке 8 в FOR
списке ( как показано здесь):
for driver.deptno in ( [10],[20],[30],[40] )
- Если есть какие-либо DEPTNO со значением 10, выполните агрегированную операцию defined (
COUNT(DRIVER.EMPNO)
) для этих строк. - Повторите эти действия для DEPTNO 20, 30 и 40.
Пункты, перечисленные в скобках в строке 8, служат не только для определения значений, для которых выполняется агрегирование; элементы также становятся именами столбцов в наборе результатов (без квадратных скобок). В SELECT
предложении решения на элементы в FOR
списке есть ссылки и псевдонимы. Если вы не укажете псевдонимы для элементов в FOR
списке, имена столбцов станут элементами в FOR
списке без скобок.
Достаточно интересно, поскольку встроенное представление DRIVER
- это всего лишь встроенное представление, вы можете поместить туда более сложный SQL. Например, рассмотрим ситуацию, когда вы хотите изменить набор результатов так, чтобы фактическое название отдела было именем столбца. Здесь перечислены строки в таблице DEPT:
select * from dept
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Вы хотите использовать PIVOT
для возврата следующего набора результатов:
ACCOUNTING RESEARCH SALES OPERATIONS
---------- ---------- ---------- ----------
3 5 6 0
Поскольку встроенное представление DRIVER
может быть практически любым допустимым табличным выражением, вы можете выполнить соединение таблицы EMP с таблицей DEPT, а затем PIVOT
оценить эти строки. Следующий запрос вернет желаемый набор результатов:
select [ACCOUNTING] as ACCOUNTING,
[SALES] as SALES,
[RESEARCH] as RESEARCH,
[OPERATIONS] as OPERATIONS
from (
select d.dname, e.empno
from emp e,dept d
where e.deptno=d.deptno
) driver
pivot (
count(driver.empno)
for driver.dname in ([ACCOUNTING],[SALES],[RESEARCH],[OPERATIONS])
) as empPivot
Как видите, PIVOT
это интересный вариант поворота наборов результатов. Независимо от того, предпочитаете ли вы использовать его традиционным методам поворота, неплохо иметь еще один инструмент в вашем наборе инструментов.