PIVOT оператор

В этом учебном материале вы узнаете, как использовать Oracle оператор PIVOT с синтаксисом и примерами.

Описание

Oracle PIVOT позволяет написать перекрестный запрос таблицы, начал использоваться в Oracle 11g. Это означает, что вы можете объединить свои результаты и повернуть строки в столбцы.

Синтаксис

Синтаксис для оператора PIVOT в Oracle/PLSQL:

SELECT * FROM
(
SELECT column1, column2
FROM tables
WHERE conditions
)
PIVOT
(
aggregate_function(column2)
FOR column2
IN ( expr1, expr2, ... expr_n) | subquery
)
ORDER BY expression [ ASC | DESC ];

Параметры или аргументы

aggregate_function

Это может быть функция, такая, как SUM, COUNT, MIN, MAX или AVG.

IN (expr1, expr2, ... expr_n)

Список значений для поворота column2 в заголовке кросс-табличного результата запроса.

subquery

Подзапрос может быть использован вместо списка значений. В этом случае результаты подзапроса будут использоваться для определения значений для поворота column2 в заголовке кросс-табличного результата запроса.

Применение

Оператор PIVOT может использоваться в следующих версиях Oracle/PLSQL:

  • Oracle 12c, Oracle 11g

Пример

Рассмотрим как использовать предложение PIVOT в Oracle.

Мы будем основывать наш пример на таблице под названием orders со следующим определением:

Для того чтобы показать вам данные для этого примера, мы будем выбирать записи из таблицы orders со следующим запросом SELECT:

Это записи таблицы orders. Мы будем использовать эти записи, чтобы продемонстрировать, как работает оператор PIVOT:

order_id customer_ref product_id
50001 SMITH 10
50002 SMITH 20
50003 ANDERSON 30
50004 ANDERSON 40
50005 JONES 10
50006 JONES 20
50007 SMITH 20
50008 SMITH 10
50009 SMITH 20

Теперь, создадим кросс-табличный запрос, используя следующий оператор PIVOT:

В этом примере, оператор PIVOT будет возвращать следующие результаты:

customer_ref 10 20 30
ANDERSON 0 0 1
JONES 1 1 0
SMITH 2 3 0

Теперь давайте разберем оператор PIVOT и объясним, как он работает.

Специфика полей для включения

Во-первых, мы хотим указать, какие поля включить в кросс-таблице. В этом примере мы хотим включить поля customer_ref и product_id. Это делается с помощью следующей части запроса:

(
SELECT customer_ref, product_id
FROM orders
)

Вы можете перечислить столбцы, которые будут включены, в любом порядке.

Специфика агрегатной функции

Далее, при создании нашего запроса кросс-таблицы, нам необходимо указать агрегатную функцию. Вы можете использовать любую из функций, например: SUM, COUNT, MIN, MAX или AVG.

В этом примере мы будем использовать функцию COUNT. Это позволит подсчитать количество значений product_id, которые соответствуют нашим критериям. Это делается с помощью следующей части запроса:

PIVOT
(
COUNT(product_id)

Специфика PIVOT значений

Наконец, мы должны указать значения PIVOT, чтобы включить в результат. Они будут использоваться в качестве заголовков столбцов в нашем кросс-табличном запросе. Чтобы указать значения PIVOT, вы можете использовать либо список значений в скобках, либо подзапрос.

В этом примере мы будем возвращать только следующие значения product_id: 10, 20, 30. В нашем кросс-табличном запросе эти значения станут нашими заголовками столбцов. Кроме того, обратите внимание, что эти значения являются конечным списком значений product_id, и не обязательно будут содержать все возможные значения.

Это делается с помощью следующей части запроса:

FOR product_id IN (10, 20, 30)
)

Теперь, когда мы совместим все это вместе, мы получим следующую PIVOT таблицу:

customer_ref 10 20 30
ANDERSON 0 0 1
JONES 1 1 0
SMITH 2 3 0