How to pivot multiple columns in postgres

Question

I have a table in Postgres that I want to pivot from wide to long on 2 columns.

The data source is too large to be able to use Python as this would require loading to memory. Is there a function in Postgres that is able to do this?

Below is what the table looks like...

date        a1_on_time      a1_days b2_on_time  b2_days
15-Apr-19   TRUE            1       TRUE        1
26-Apr-19   TRUE            2       FALSE       6

The output should look like this:

date        metric   on_time   days
15-Apr-19   a1       TRUE      1
26-Apr-19   a1       TRUE      2
15-Apr-19   b2       TRUE      1
26-Apr-19   b2       FALSE     6

Any ideas would be much appreciated.

Tim Biegeleisen · Accepted Answer · 2019-05-24 09:11:19Z

1

Use a union query:

select date, 'a1' as metric, a1_on_time as on_time, a1_days as days from your_table
union all
select date, 'b2', b2_on_time, b2_days from your_table
order by metric, date;

answered May 24, 2019 at 9:11

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to pivot multiple columns in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related