Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle PIVOT

Oracle PIVOT

Summary: in this tutorial, you will learn how to use the Oracle PIVOT clause to transpose rows to columns to generate result sets in crosstab format.

Introduction to Oracle PIVOT clause

Oracle 11g introduced the new PIVOT clause that allows you to write cross-tabulation queries which transpose rows into columns, aggregating data in the process of the transposing. As a result, the output of a pivot operation returns more columns and fewer rows than the starting data set.

Oracle PIVOT

The following illustrates the basic syntax of the Oracle PIVOT clause:

SELECT select_list FROM table_name PIVOT [XML] ( pivot_clause pivot_for_clause pivot_in_clause );

In this syntax, following the PIVOT keyword are three clauses:

  • pivot_clause specifies the column(s) that you want to aggregate. The pivot_clause performs an implicitly GROUP BY based on all columns which are not specified in the clause, along with values provided by the pivot_in_clause.
  • pivot_for_clause specifies the column that you want to group or pivot.
  • pivot_in_clause defines a filter for column(s) in the pivot_for_clause. The aggregation for each value in the pivot_in_clause will be rotated into a separate column.

Oracle PIVOT example

Let’s create a new view named order_stats that includes product category, order status, and order id for demonstration.

CREATE VIEW order_stats AS SELECT category_name, status, order_id FROM order_items INNER JOIN orders USING (order_id) INNER JOIN products USING (product_id) INNER JOIN product_categories USING (category_id);

Here is the partial data from the order_stats view:

SELECT * FROM order_stats;
oracle pivot sample view partial data

This example uses the PIVOT clause to return the number of orders for each product category by order status:

SELECT * FROM order_stats PIVOT( COUNT(order_id) FOR category_name IN ( 'CPU', 'Video Card', 'Mother Board', 'Storage' ) ) ORDER BY status;

In this example:

  • The COUNT(order_id) is the pivot_clause.
  • FOR category_name is the pivot_for_clause.

And here is the pivot_in_clause:

IN ( 'CPU', 'Video Card', 'Mother Board', 'Storage' )

The COUNT() function returns the number of orders by category and order status. The query uses the values specified in the pivot_in_clause for the column headings of the result set.

Here is the output:

Oracle PIVOT example

Aliasing pivot columns

In the previous example, Oracle used product categories to generate pivot column names. On the other hands, you can alias one or more columns in the pivot_clause and one or more values in the pivot_in_clause.

Generally, Oracle uses the following convention to name the pivot columns based on aliases:

Pivot Column Aliased?Pivot In-Value Aliased?Pivot Column Name
NoNopivot_in_clause value
YesYes
pivot_in_clause alias || ‘_’ || pivot_clause alias
NoYespivot_in_clause alias
YesNo
pivot_in_clause value || ‘_’ || pivot_clause alias

The following statement uses the query example above with the aliases:

SELECT * FROM order_stats PIVOT( COUNT(order_id) order_count FOR category_name IN ( 'CPU' CPU, 'Video Card' VideoCard, 'Mother Board' MotherBoard, 'Storage' Storage ) ) ORDER BY status;

Here is the result set:

Oracle PIVOT aliasing example

As you can see, the pivot column names follow the below naming convention:

pivot_in_clause alias || '_' || pivot_clause alias

Note that if you use more than one aggregate function in the pivot_clause, you must provide aliases for at least one of the aggregate functions.

Pivoting multiple columns

In the previous example, you have seen that we used one aggregate function in the pivot_clause. In the following example, we will use two aggregate functions.

First, alter the order_stats view to include the order value column:

CREATE OR REPLACE VIEW order_stats AS SELECT category_name, status, order_id, SUM(quantity * list_price) AS order_value FROM order_items INNER JOIN orders USING (order_id) INNER JOIN products USING (product_id) INNER JOIN product_categories USING (category_id) GROUP BY order_id, status, category_name;

Second, query data from the new order_stats view:

SELECT * FROM order_stats;
oracle pivot sample view

Third, use PIVOT clause to return the number of orders and order values by product category and order status:

SELECT * FROM order_stats PIVOT( COUNT(order_id) orders, SUM(order_value) sales FOR category_name IN ( 'CPU' CPU, 'Video Card' VideoCard, 'Mother Board' MotherBoard, 'Storage' Storage ) ) ORDER BY status;

Here is the output:

As you can see from the output, the number of pivot columns is doubled, combining category_name with orders and sales.

Finally, use status as the pivot columns and category_name as rows:

SELECT * FROM order_stats PIVOT( COUNT(order_id) orders, SUM(order_value) sales FOR status IN ( 'Canceled' Canceled, 'Pending' Pending, 'Shipped' Shipped ) ) ORDER BY category_name;

The following picture shows the output:

oracle pivot - pivoting multiple columns example 2

Oracle PIVOT with subquery

You cannot use a subquery in the pivot_in_clause. The following statement is invalid and causes an error:

SELECT * FROM order_stats PIVOT( COUNT(order_id) orders, SUM(order_value) sales FOR category_name IN ( SELECT category_name FROM product_categories ) ) ORDER BY status;

Here is the error message:

ORA-00936: missing expression

This restriction is relaxed with the XML option:

SELECT * FROM order_stats PIVOT XML ( COUNT(order_id) orders, SUM(order_value) sales FOR category_name IN ( SELECT category_name FROM product_categories ) ) ORDER BY status;

This picture is the output:

oracle pivot xml

Here is the sample of one PivotSet:

<PivotSet> <item> <column name="CATEGORY_NAME">CPU</column> <column name="ORDERS">13</column> <column name="SALES">4122040.7</column> </item> <item> <column name="CATEGORY_NAME">Mother Board</column> <column name="ORDERS">12</column> <column name="SALES">679121.39</column> </item> <item> <column name="CATEGORY_NAME">RAM</column> <column name="ORDERS">0</column> <column name="SALES" /> </item> <item> <column name="CATEGORY_NAME">Storage</column> <column name="ORDERS">14</column> <column name="SALES">3023747.6</column> </item> <item> <column name="CATEGORY_NAME">Video Card</column> <column name="ORDERS">9</column> <column name="SALES">1677597.4</column> </item> </PivotSet>

To view XML in the output grid of from the SQL Developer, you follow these steps to set it up:

1)  From the Tool menu, select Preferences

SQL Developer Tool - Preferences

2) Under Database > Advanced, check the option Display XML Value in Grid

SQL Developer Display XML in Grid

The XML output format is not the same as non-XML pivot one. For each value specified in the pivot_in_clause, the subquery returns a single XML string column.

The XML string for each row contains aggregated data corresponding to the implicit GROUP BY value of that row e.g., number of orders ( ORDERS) and total sales ( SALES).

When you use a subquery in the pivot_in_clause, Oracle uses all values returned by the subquery for pivoting.

Note that the subquery must return a list of unique values. Otherwise, Oracle will raise a run-time error. If you are not sure whether the subquery returns a list of distinct values or not, you can use he DISTINCT keyword in the subquery.

In this tutorial, you have learned how to use the Oracle PIVOT clause to transpose rows to columns to make crosstab reports.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle ROLLUP
Next Oracle UNPIVOT

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.