PIVOT and UNPIVOT in the Oracle Database

This week's throwback feature of the week is a simple and relatively well known SQL Operator. It's the ability to pivot and unpivot SQL columns inside a table. Pivoting and Unpivoting inside a table is an easy way to change how your data is presented.

This feature was released in Oracle Database 11g

What are PIVOT and UNPIVOT?

Simply put, PIVOT rotates rows into columns, while UNPIVOT does the opposite, it turns columns back into rows. This can be really useful for reporting and analytical models where you'd like to summarize data for comparison.

Let's look at a simple example.

Using PIVOT and UNPIVOT

Imagine you're building a simple dashboard that tracks company sales across each quarter and region. In this example, your sales are stored in rows, but our dashboard needs to look at the quarterly comparisons in columns.

CREATE TABLE sales_transactions (
	transaction_id NUMBER,
	region_name    VARCHAR2(20),
	quarter_name   VARCHAR2(6),
	sales_amount   NUMBER(10,2)
);

We can add sample data. (NOTE: this syntax uses the updated table value constructors in Oracle Database 23ai. If you're trying this on a version prior to 23ai, you will need to add an insert statement for each row).

INSERT INTO sales_transactions (id, region, quarter, amount) VALUES
(1, 'North America', 'Q1', 120000.00),
(2, 'North America', 'Q2', 145000.00),
(3, 'North America', 'Q3', 130000.00),
(4, 'North America', 'Q4', 160000.00),
(5, 'Europe', 'Q1', 95000.00),
(6, 'Europe', 'Q2', 105000.00),
(7, 'Europe', 'Q3', 120000.00),
(8, 'Europe', 'Q4', 140000.00),
(9, 'Asia Pacific', 'Q1', 80000.00),
(10, 'Asia Pacific', 'Q2', 90000.00),
(11, 'Asia Pacific', 'Q3', 110000.00),
(12, 'Asia Pacific', 'Q4', 125000.00),
(13, 'Latin America', 'Q1', 45000.00),
(14, 'Latin America', 'Q2', 55000.00),
(15, 'Latin America', 'Q3', 65000.00),
(16, 'Latin America', 'Q4', 70000.00);

Our data initially looks like this:

Using PIVOT:

Now, we can use the PIVOT operator and look at our total sales amounts for each quarter by region

SELECT * FROM
	(SELECT region_name, quarter_name, sales_amount
	FROM sales_transactions)
PIVOT (SUM(sales_amount) AS sales
	FOR quarter_name IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4));

PIVOT gives us a nice summarized view of our total sales for each one of our regions over the last four quarters. This format fits our dashboard needs.

We could also calculate the quarter-over-quarter growth percentages using PIVOT.

SELECT region_name,
		q1_sales,
q2_sales,
		ROUND(((q2_sales - q1_sales) / q1_sales) * 100, 1) AS q1_to_q2_growth,
		q3_sales,
		ROUND(((q3_sales - q2_sales) / q2_sales) * 100, 1) AS q2_to_q3_growth,
		q4_sales,
		ROUND(((q4_sales - q3_sales) / q3_sales) * 100, 1) AS q3_to_q4_growth
FROM	(SELECT region_name, quarter_name, sales_amount
		FROM   sales_transactions)
PIVOT	(SUM(sales_amount) AS sales
		FOR quarter_name IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4));

Using UNPIVOT:

The UNPIVOT works when we have our data in columns and want it represented in rows.

CREATE TABLE quarterly_budget_forecast (
	region_name VARCHAR2(20),
	q1_budget NUMBER(10,2),
	q2_budget NUMBER(10,2),
	q3_budget NUMBER(10,2),
	q4_budget NUMBER(10,2)
);

We can add sample data again

INSERT INTO quarterly_budget_forecast VALUES 
	('North America', 125000, 150000, 140000, 170000),
	('Europe', 100000, 110000, 125000, 145000),
	('Asia Pacific', 85000, 95000, 115000, 130000),
	('Latin America', 50000, 60000, 70000, 75000);

We can use UNPIVOT to see data in individual rows

SELECT region_name,
		quarter_name,
		budget_amount
FROM quarterly_budget_forecast
UNPIVOT (budget_amount FOR quarter_name IN (
	q1_budget AS 'Q1',
	q2_budget AS 'Q2',
	q3_budget AS 'Q3',
	q4_budget AS 'Q4'))
ORDER BY region_name, quarter_name;

Clean up

You can clean up with the following

DROP table quarterly_budget_forecast;
DROP table sales_transactions;

Recap

PIVOT and UNPIVOT may not be the flashiest feature out there but they're simple to use and are nice when we need to swap rows or columns. You can also perform both of the operators while using joins as well.

For another example, check out the blog Chris wrote here.

You can try this demo out on Oracle LiveSQL for a free SQL prompt and editor.

-Killian