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 );
Code language: SQL (Structured Query Language) (sql)

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 );
Code language: SQL (Structured Query Language) (sql)

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);
Code language: SQL (Structured Query Language) (sql)

Third, query data from the sale_stats table:

SELECT * FROM sale_stats;
Code language: SQL (Structured Query Language) (sql)

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' ) );
Code language: SQL (Structured Query Language) (sql)

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' )
Code language: SQL (Structured Query Language) (sql)

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' ) );
Code language: SQL (Structured Query Language) (sql)

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) );
Code language: SQL (Structured Query Language) (sql)

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);
Code language: SQL (Structured Query Language) (sql)

Third, query data from the sale_stats table:

SELECT * FROM sale_stats;
Code language: SQL (Structured Query Language) (sql)

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' ) );
Code language: SQL (Structured Query Language) (sql)

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?