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_idcustomer_refproduct_id
50001SMITH10
50002SMITH20
50003ANDERSON30
50004ANDERSON40
50005JONES10
50006JONES20
50007SMITH20
50008SMITH10
50009SMITH20

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

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

customer_ref102030
ANDERSON001
JONES110
SMITH230

Теперь давайте разберем оператор 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_ref102030
ANDERSON001
JONES110
SMITH230