Wednesday, April 24, 2024

Oracle / PLSQL: PIVOT

 

Oracle / PLSQL: PIVOT Clause

This Oracle tutorial explains how to use the Oracle PIVOT clause with syntax and examples.

Description

The Oracle PIVOT clause allows you to write a cross-tabulation query starting in Oracle 11g. This means that you can aggregate your results and rotate rows into columns.

Syntax

The syntax for the PIVOT clause in Oracle/PLSQL is:

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 ];

Parameters or Arguments

aggregate_function
It can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.
Example
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)
);

To show you the data for this example, we will select the records from the orders table with the following SELECT statement:

SELECT order_id, customer_ref, product_id
FROM orders
ORDER BY order_id;

These are the records in the orders table. We will be using these records to demonstrate how the PIVOT clause works:

order_idcustomer_refproduct_id
50001SMITH10
50002SMITH20
50003ANDERSON30
50004ANDERSON40
50005JONES10
50006JONES20
50007SMITH20
50008SMITH10
50009SMITH20

Now, let's create our cross-tabulation query using the following PIVOT clause:

SELECT * FROM
(
  SELECT customer_ref, product_id
  FROM orders
)
PIVOT
(
  COUNT(product_id)
  FOR product_id IN (10, 20, 30)
)
ORDER BY customer_ref;

In this example, the PIVOT clause would return the following results:

customer_ref102030
ANDERSON001
JONES110
SMITH230

Now, let's break apart the PIVOT clause and explain how it worked.

Specify Fields to Include

First, we want to specify what fields to include in our cross tabulation. In this example, we want to include the customer_ref and product_id fields. This is done by the following portion of the statement:

(
  SELECT customer_ref, product_id
  FROM orders
)

No comments:

Post a Comment