This article looks at the UNPIVOT function in Oracle and how to reproduce its result using PostgreSQL.
1. UNPIVOT in Oracle
2. Replicating UNPIVOT in PostgreSQL
Oracle has an UNPIVOT directive, which transposes columns into rows. PostgreSQL doesn't support this syntax or functionality, but we can easily get the same result.
UNPIVOT in Oracle
First, let's look at an example in Oracle. We will create a table with some simple sales data. We will record the department year and sales amount for each quarter.
CREATE TABLE sales (
id int PRIMARY KEY,
department varchar(32),
year int,
q1 numeric(10,2),
q2 numeric(10,2),
q3 numeric(10,2),
q4 numeric(10,2)
);
INSERT INTO sales (id, department, year, q1, q2, q3, q4)
VALUES (1, 'fashion', 2019, 43221.33, 22925.39, 51392.90, 79121.65);
INSERT INTO sales (id, department, year, q1, q2, q3, q4)
VALUES (2, 'tech', 2019, 131932.87, 101932.69, 184938.78, 198211.18);
Now we will use UNPIVOT to convert the quarterly columns into row values:
SELECT * FROM sales
UNPIVOT (
amount
FOR quarter
IN (
q1 AS 'Q1',
q2 AS 'Q2',
q3 AS 'Q3',
q4 AS 'Q4'
)
);
| ID | DEPARTMENT | YEAR | QUARTER | AMOUNT |
|----|------------|------|---------|-----------|
| 1 | fashion | 2019 | Q1 | 43221.33 |
| 1 | fashion | 2019 | Q2 | 22925.39 |
| 1 | fashion | 2019 | Q3 | 51392.9 |
| 1 | fashion | 2019 | Q4 | 79121.65 |
| 2 | tech | 2019 | Q1 | 131932.87 |
| 2 | tech | 2019 | Q2 | 101932.69 |
| 2 | tech | 2019 | Q3 | 184938.78 |
| 2 | tech | 2019 | Q4 | 198211.18 |
Replicating UNPIVOT in PostgreSQL
To achieve this result in PostgreSQL, we will define an array of values that will act as the values that represent each quarterly column, followed by an array that specifies each of the columns that correspond to those quarters. We will unnest both of those arrays, and that will give us paired values for each quarter and the amount of that quarter.
SELECT
id,
department,
year,
unnest(array['Q1', 'Q2', 'Q3', 'Q4']) AS quarter,
unnest(array[q1, q2, q3, q4]) AS amount
FROM
sales;
| id | department | year | quarter | amount |
|----|------------|------|---------|-----------|
| 1 | fashion | 2019 | Q1 | 43221.33 |
| 1 | fashion | 2019 | Q2 | 22925.39 |
| 1 | fashion | 2019 | Q3 | 51392.9 |
| 1 | fashion | 2019 | Q4 | 79121.65 |
| 2 | tech | 2019 | Q1 | 131932.87 |
| 2 | tech | 2019 | Q2 | 101932.69 |
| 2 | tech | 2019 | Q3 | 184938.78 |
| 2 | tech | 2019 | Q4 | 198211.18 |
As you can see, we end up with exactly the same result.
An alternative is to use a LATERAL join, providing rows that pair up the value to represent the quarter with the quarterly column:
SELECT
id,
department,
year,
quarter,
amount
FROM
sales,
LATERAL (VALUES('Q1', q1),('Q2', q2),('Q3', q3),('Q4', q4)) AS unpivot(quarter, amount);
| id | department | year | quarter | amount |
|----|------------|------|---------|-----------|
| 1 | fashion | 2019 | Q1 | 43221.33 |
| 1 | fashion | 2019 | Q2 | 22925.39 |
| 1 | fashion | 2019 | Q3 | 51392.9 |
| 1 | fashion | 2019 | Q4 | 79121.65 |
| 2 | tech | 2019 | Q1 | 131932.87 |
| 2 | tech | 2019 | Q2 | 101932.69 |
| 2 | tech | 2019 | Q3 | 184938.78 |
| 2 | tech | 2019 | Q4 | 198211.18 |
Note that the term "unpivot" shown in the query text above isn't a function; it's just an arbitrary label to give to the false table. To clarify, the quarter and amount in the SELECT part of the query could be explicitly stated as “unpivot.quarter” and “unpivot.amount.”
There are further ways to achieve equivalent functionality to Oracle's UNPIVOT, but as you can see, PostgreSQL is flexible enough to achieve the same functionality and in various ways.