How to use Pivot in postgres

Question

I need to use pivot in postgres, below is the base table

Below is the desired output

Please help me with the query.

http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — , Mar 04 '16 at 13:46

score 1 · Accepted Answer · answered Mar 04 '16 at 13:52

1

This is actually an un-pivot, not pivot

select year, week, 'loading' as area, loading as value
from the_table
union all
select year, week, 'picking', picking
from the_table
union all
select year, week, 'painting', painting
from the_table

answered Mar 04 '16 at 13:52

Thank you so much.. saved me – Anshul Mar 04 '16 at 14:17

score 0 · Answer 2 · edited May 23 '17 at 12:23

If you have only 3 columns which you need to pivot, then use union

select year,week,'loading' as aread,loading as val from tbl
union all
select year,week,'painting' as area,painting as val from tbl
union all
select year,week,'picking' as area,picking as val from tbl

If the number of column is dynamic, then I 'd suggest you to use dynamic pivot.

Dynamic pivot query using PostgreSQL 9.3

http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/

How to use Pivot in postgres

2 Answers2