Создание кросс-табличного отчета с помощью оператора PIVOT в SQL Server

Проблема

Вы хотите создать кросс-табличный отчет, чтобы преобразовать строки набора результатов в столбцы.

Вы знакомы с традиционными методами поворота (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] )
  1. Если есть какие-либо DEPTNO со значением 10, выполните агрегированную операцию defined ( COUNT(DRIVER.EMPNO)) для этих строк.
  2. Повторите эти действия для 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 это интересный вариант поворота наборов результатов. Независимо от того, предпочитаете ли вы использовать его традиционным методам поворота, неплохо иметь еще один инструмент в вашем наборе инструментов.

Вас заинтересует / Intresting for you:

SQL: Правила выполнения однота...
SQL: Правила выполнения однота... 49 просмотров Дэйзи ак-Макарова Sat, 31 Jul 2021, 06:47:05
Значения NULL в языке SQL: отс...
Значения NULL в языке SQL: отс... 318 просмотров Antoni Mon, 21 Jun 2021, 19:57:18
Операции SQL в базе данных Ora...
Операции SQL в базе данных Ora... 4441 просмотров Antoni Wed, 11 Apr 2018, 12:22:28
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 7898 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Войдите чтобы комментировать