Convert columns data into rows in PostgreSQL

Question

I have data in the following format.

order_no    rate       jan        feb       mar     ....
 1           1200                  2         4
 2           1000      1                     5
 3           2400      14          3

Now I want to transpose this table to get the following output.

order_no    rate       month     unit
 1           1200       feb       2 
 1           1200       mar       4 
 2           1000       jan       1
 2           2400       mar       5  and so on..

How can I do this?

score 5 · Accepted Answer · answered Jul 18 '18 at 11:32

You can create a "temporary" normalized view on the data using a cross join:

select o.order_no, o.rate, v.*
from orders o
  cross join lateral (
      values 
        ('jan', jan), 
        ('feb', feb), 
        ('mar', mar), 
        ... 
        ('dec', dec)
  ) as v(month, unit)

If you want to exclude the months with no values, you can add

where v.unit is not null

to the query

Online example: http://rextester.com/PBP46544

score 0 · Answer 2 · answered Jul 18 '18 at 11:27

One simple approach uses UNION:

SELECT order_no, rate, 'jan' AS month, jan AS unit UNION ALL
SELECT order_no, rate, 'feb', feb UNION ALL
...
SELECT order_no, rate, 'dec', dec
ORDER BY order_no;

Postgres also has CROSSTAB capabilities. But to use that, you have to be really good at SQL, which I'm not.

score 0 · Answer 3 · answered Jul 18 '18 at 11:30

Try this

Select  order_no, rate, 'jan' as month, jan as unit
from tbl
where jan is not null
union all
Select  order_no, rate, 'feb' as month, feb as unit
from tbl
where feb is not null
union all
Select  order_no, rate, 'mar' as month, mar as unit
from tbl
where mar is not null
order by order_no

Convert columns data into rows in PostgreSQL

3 Answers3