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 UNPIVOT

Oracle UNPIVOT

Summary: in this tutorial, you will learn how to use the Oracle UNPIVOT clause to transpose columns to rows.

Introduction to Oracle UNPIVOT clause

The Oracle UNPIVOT clause allows you to transpose columns to rows. The UNPIVOT clause is opposite to the PIVOT clause except that it does not de-aggregate data during the transposing process.

Oracle UNPIVOT

The following illustrates the syntax of Oracle UNPIVOT clause:

SELECT select_list FROM table_name UNPIVOT [INCLUDE | EXCLUDE NULLS]( unpivot_clause unpivot_for_clause unpivot_in_clause );

In this syntax:

  • The unpivot_clause allows you to specify a name for a column that represents the unpivoted measure values.
  • The unpivot_for_clause allows you to specify the name for each column that will hold the measure’s values.
  • The unpivot_in_clause contains the pivoted columns that will be unpivoted.

The INCLUDE | EXCLUDE NULLS clause allows you to include or exclude null-valued rows.

  • The INCLUDE NULLS clause instructs Oracle to include null-valued rows.
  • The EXCLUDE NULLS clause, on the other hand, eliminates null-valued rows from the returned result set.

By default, the unpivot operation excludes null-valued rows.

Let’s take some examples of using the Oracle UNPIVOT clause to get a better understanding.

Setting up a sample table

First, create a new table called sale_stats for demonstration:

CREATE TABLE sale_stats( id INT PRIMARY KEY, fiscal_year INT, product_a INT, product_b INT, product_c INT );

Second, insert some rows into the sale_stats table:

INSERT INTO sale_stats(id, fiscal_year, product_a, product_b, product_c) VALUES(1,2017, NULL, 200, 300); INSERT INTO sale_stats(id, fiscal_year, product_a, product_b, product_c) VALUES(2,2018, 150, NULL, 250); INSERT INTO sale_stats(id, fiscal_year, product_a, product_b, product_c) VALUES(3,2019, 150, 220, NULL);

Third, query data from the sale_stats table:

SELECT * FROM sale_stats;

Here is the output:

oracle unpivot - sample table

Oracle UNPIVOT examples

This statement uses the UNPIVOT clause to rotate columns product_a, product_b, and product_c into rows:

SELECT * FROM sale_stats UNPIVOT( quantity -- unpivot_clause FOR product_code -- unpivot_for_clause IN ( -- unpivot_in_clause product_a AS 'A', product_b AS 'B', product_c AS 'C' ) );

In this example:

The unpivot_clause is quantity which is a column that represents the unpivoted values from the product_a, product_b, and product_c columns.

The unpivot_for_clause is FOR product_code, which is the column that will hold the measure’s values.

The unpivot_in_clause clause is:

IN ( -- unpivot_in_clause product_a AS 'A', product_b AS 'B', product_c AS 'C' )

which instructs Oracle to unpivot values in the product_a, product_b, and product_c columns.

The following picture shows the output:

oracle unpivot - example

By default, the UNPIVOT operation excludes null-valued rows, therefore, you don’t see any NULL in the output.

The following example uses the UNPIVOT clause to transpose values in the columns product_a, product_b, and product_c to rows, but including null-valued rows:

SELECT * FROM sale_stats UNPIVOT INCLUDE NULLS( quantity FOR product_code IN ( product_a AS 'A', product_b AS 'B', product_c AS 'C' ) );

Here is the output:

oracle unpivot include nulls example

Oracle unpivot multiple columns

Let’s see an example of unpivoting multiple columns.

First, drop and recreate the sale_stats table:

DROP TABLE sale_stats; CREATE TABLE sale_stats( id INT PRIMARY KEY, fiscal_year INT, a_qty INT, a_value DEC(19,2), b_qty INT, b_value DEC(19,2) );

Second, insert rows into the sale_stats table:

INSERT INTO sale_stats(id, fiscal_year, a_qty, a_value, b_qty, b_value) VALUES(1, 2018, 100, 1000, 2000, 4000); INSERT INTO sale_stats(id, fiscal_year, a_qty, a_value, b_qty, b_value) VALUES(2, 2019, 150, 1500, 2500, 5000);

Third, query data from the sale_stats table:

SELECT * FROM sale_stats;

Finally, use the UNPIVOT clause to transpose the values in the column a_qty, a_value, b_qty, and b_value into rows:

SELECT * FROM sale_stats UNPIVOT ( (quantity, amount) FOR product_code IN ( (a_qty, a_value) AS 'A', (b_qty, b_value) AS 'B' ) );

Here is the result set:

oracle unpivot multiple columns example

This picture illustrates the transposing process:

Oracle unpivot multiple columns

In this tutorial, you have learned how to use the Oracle UNPIVOT clause to transpose columns to rows.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle PIVOT
Next Oracle INSERT

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.