Dynamic unpivot with Postgres

In my previous post about unpivot in Postgres I showed how this can be done in a compact manner without using a series of UNION statements.

But Postgres offers an even more compact and dynamic way to do this.

When the unpivot should be used with columns that have different data types, all of them have to be cast to text (or varchar). In this case the unpivot can be done in a fully automatic way without the need to spell out each column.

Let’s take the table from the previous example:

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

With Postgres’ JSON functions the unpivot (and casting) can be done in the following way:

select c.customer_id, t.quarter, t.value
from customer_turnover c
  cross join lateral jsonb_each_text(to_jsonb(c) - 'customer_id') as t(quarter, value);
order by customer_id, t.quarter;  

The expression to_jsonb(c) will turn the entire row into a JSON value where the column names are the keys. The values will be cast to text. The expression -'customer_id' then removes the key "customer_id" so that only the columns we are interested in, are left in the JSON value.

The function jsonb_each_text() then turns this JSON value into a multiple rows with two columns (key and value).

So the above will return the following result.

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

The nice thing about this solution is, that new columns will automatically be included in the output, without the need to change the SQL query.