Unpivot with Postgres

Page content

Sometimes it’s necessary to normalize de-normalized tables - the opposite of a “crosstab” or “pivot” operation. Postgres does not support the standard UNPIVOT operator, 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 standard SQL 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 PIVOT 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;