В этом учебном материале вы узнаете, как использовать Oracle оператор PIVOT с синтаксисом и примерами.
Описание
Oracle PIVOT позволяет написать перекрестный запрос таблицы, начал использоваться в Oracle 11g. Это означает, что вы можете объединить свои результаты и повернуть строки в столбцы.
Синтаксис
Синтаксис для оператора PIVOT в Oracle/PLSQL:
(
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 со следующим определением:
|
1 2 3 4 5 6 7 8 |
CREATE TABLE orders ( order_id integer NOT NULL, customer_ref varchar2(50) NOT NULL, order_date date, product_id integer, quantity integer, CONSTRAINT orders_pk PRIMARY KEY (order_id) ); |
Для того чтобы показать вам данные для этого примера, мы будем выбирать записи из таблицы orders со следующим запросом SELECT:
|
1 2 3 |
SELECT order_id, customer_ref, product_id FROM orders ORDER BY order_id; |
Это записи таблицы 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:
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT * FROM ( SELECT customer_ref, product_id FROM orders ) PIVOT ( COUNT(product_id) FOR product_id IN (10, 20, 30) ) ORDER BY customer_ref; |
В этом примере, оператор 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, которые соответствуют нашим критериям. Это делается с помощью следующей части запроса:
(
COUNT(product_id)
Специфика PIVOT значений
Наконец, мы должны указать значения PIVOT, чтобы включить в результат. Они будут использоваться в качестве заголовков столбцов в нашем кросс-табличном запросе. Чтобы указать значения PIVOT, вы можете использовать либо список значений в скобках, либо подзапрос.
В этом примере мы будем возвращать только следующие значения product_id: 10, 20, 30. В нашем кросс-табличном запросе эти значения станут нашими заголовками столбцов. Кроме того, обратите внимание, что эти значения являются конечным списком значений product_id, и не обязательно будут содержать все возможные значения.
Это делается с помощью следующей части запроса:
)
Теперь, когда мы совместим все это вместе, мы получим следующую PIVOT таблицу:
| customer_ref | 10 | 20 | 30 |
|---|---|---|---|
| ANDERSON | 0 | 0 | 1 |
| JONES | 1 | 1 | 0 |
| SMITH | 2 | 3 | 0 |