Equivalent to unpivot() in PostgreSQL

Question

Create an example table:

CREATE TEMP TABLE foo (id int, a text, b text, c text);
INSERT INTO foo VALUES (1, 'ant', 'cat', 'chimp'), (2, 'grape', 'mint', 'basil');

You can 'unpivot' or 'uncrosstab' using UNION ALL:

SELECT id,
       'a' AS colname,
       a AS thing
FROM foo
UNION ALL
SELECT id,
       'b' AS colname, 
       b AS thing
FROM foo
UNION ALL
SELECT id, 
       'c' AS colname,
       c AS thing
FROM foo
ORDER BY id;

This runs 3 different subqueries on foo, one for each column we want to unpivot, and returns, in one table, every record from each of the subqueries.

But that will scan the table N times, where N is the number of columns you want to unpivot. This is inefficient, and a big problem when, for example, you're working with a very large table that takes a long time to scan.

Instead, use:

SELECT id,
       unnest(array['a', 'b', 'c']) AS colname,
       unnest(array[a, b, c]) AS thing
FROM foo
ORDER BY id;

This is easier to write, and it will only scan the table once.

array[a, b, c] returns an array object, with the values of a, b, and c as it's elements. unnest(array[a, b, c]) breaks the results into one row for each of the array's elements.

Hope that helps!

You could use VALUES() and JOIN LATERAL to unpivot the columns.

Sample data:

CREATE TABLE test(id int, a INT, b INT, c INT);
INSERT INTO test(id,a,b,c) VALUES (1,11,12,13),(2,21,22,23),(3,31,32,33);

Query:

SELECT t.id, s.col_name, s.col_value
FROM test t
JOIN LATERAL(VALUES('a',t.a),('b',t.b),('c',t.c)) s(col_name, col_value) ON TRUE;

DBFiddle Demo

Using this approach it is possible to unpivot multiple groups of columns at once.

EDIT

Using Zack's suggestion:

SELECT t.id, col_name, col_value
FROM test t
CROSS JOIN LATERAL (VALUES('a', t.a),('b', t.b),('c',t.c)) s(col_name, col_value);

<=>

SELECT t.id, col_name, col_value
FROM test t
,LATERAL (VALUES('a', t.a),('b', t.b),('c',t.c)) s(col_name, col_value);

db<>fiddle demo

Great article by Thomas Kellerer found here

Unpivot with Postgres

Sometimes it’s necessary to normalize de-normalized tables - the opposite of a “crosstab” or “pivot” operation. Postgres does not support an UNPIVOT operator like Oracle or SQL Server, but simulating it, is very simple.

Take the following table that stores aggregated values per quarter:

create table customer_turnover
(
  customer_id   integer,
  q1            integer,
  q2            integer,
  q3            integer,
  q4            integer
);

And the following sample data:

customer_id | q1  | q2  | q3  | q4 
------------+-----+-----+-----+----
          1 | 100 | 210 | 203 | 304
          2 | 150 | 118 | 422 | 257
          3 | 220 | 311 | 271 | 269

But we want the quarters to be rows (as they should be in a normalized data model).

In Oracle or SQL Server this could be achieved with the UNPIVOT operator, but that is not available in Postgres. However Postgres’ ability to use the VALUES clause like a table makes this actually quite easy:

select c.customer_id, t.*
from customer_turnover c
  cross join lateral (
     values 
       (c.q1, 'Q1'),
       (c.q2, 'Q2'),
       (c.q3, 'Q3'),
       (c.q4, 'Q4')
  ) as t(turnover, quarter)
order by customer_id, quarter;

will return the following result:

customer_id | turnover | quarter
------------+----------+--------
          1 |      100 | Q1     
          1 |      210 | Q2     
          1 |      203 | Q3     
          1 |      304 | Q4     
          2 |      150 | Q1     
          2 |      118 | Q2     
          2 |      422 | Q3     
          2 |      257 | Q4     
          3 |      220 | Q1     
          3 |      311 | Q2     
          3 |      271 | Q3     
          3 |      269 | Q4     

The equivalent query with the standard UNPIVOT operator would be:

select customer_id, turnover, quarter
from customer_turnover c
  UNPIVOT (turnover for quarter in (q1 as 'Q1', 
                                    q2 as 'Q2', 
                                    q3 as 'Q3',
                                    q4 as 'Q4'))
order by customer_id, quarter;

FYI for those of us looking for how to unpivot in RedShift.

The long form solution given by Stew appears to be the only way to accomplish this.

---

For those who cannot see it there, here is the text pasted below:

We do not have built-in functions that will do pivot or unpivot. However, you can always write SQL to do that.

create table sales (regionid integer, q1 integer, q2 integer, q3 integer, q4 integer);
insert into sales values (1,10,12,14,16), (2,20,22,24,26);

select * from sales order by regionid;

 regionid | q1 | q2 | q3 | q4
----------+----+----+----+----
 1        | 10 | 12 | 14 | 16
 2        | 20 | 22 | 24 | 26
(2 rows)

pivot query

create table sales_pivoted (regionid, quarter, sales)
as
select regionid, 'Q1', q1 from sales
UNION ALL
select regionid, 'Q2', q2 from sales
UNION ALL
select regionid, 'Q3', q3 from sales
UNION ALL
select regionid, 'Q4', q4 from sales
;

select * from sales_pivoted order by regionid, quarter;

 regionid | quarter | sales 
----------+---------+-------
 1        | Q1      | 10
 1        | Q2      | 12
 1        | Q3      | 14
 1        | Q4      | 16
 2        | Q1      | 20
 2        | Q2      | 22
 2        | Q3      | 24
 2        | Q4      | 26
(8 rows)

unpivot query

select regionid, sum(Q1) as Q1, sum(Q2) as Q2, sum(Q3) as Q3, sum(Q4) as Q4
from
(select regionid, 
case quarter when 'Q1' then sales else 0 end as Q1,
case quarter when 'Q2' then sales else 0 end as Q2,
case quarter when 'Q3' then sales else 0 end as Q3,
case quarter when 'Q4' then sales else 0 end as Q4
from sales_pivoted)

group by regionid
order by regionid;

 regionid | q1 | q2 | q3 | q4 
----------+----+----+----+----
 1        | 10 | 12 | 14 | 16
 2        | 20 | 22 | 24 | 26
(2 rows)

Hope this helps, Neil

Pulling slightly modified content from the link in the comment from @a_horse_with_no_name into an answer because it works:

Installing Hstore
If you don't have hstore installed and are running PostgreSQL 9.1+, you can use the handy

CREATE EXTENSION hstore;

For lower versions, look for the hstore.sql file in share/contrib and run in your database.

Assuming that your source (e.g., wide data) table has one 'id' column, named id_field, and any number of 'value' columns, all of the same type, the following will create an unpivoted view of that table.

CREATE VIEW vw_unpivot AS 
SELECT id_field, (h).key AS column_name, (h).value AS column_value
  FROM (
    SELECT id_field, each(hstore(foo) - 'id_field'::text) AS h 
      FROM zcta5 as foo  
  ) AS unpiv ; 

This works with any number of 'value' columns. All of the resulting values will be text, unless you cast, e.g., (h).value::numeric.