Проблема
Вы хотите создать кросс-табличный отчет, чтобы преобразовать строки набора результатов в столбцы.
Вы знакомы с традиционными методами поворота (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 это интересный вариант поворота наборов результатов. Независимо от того, предпочитаете ли вы использовать его традиционным методам поворота, неплохо иметь еще один инструмент в вашем наборе инструментов.
