Search code examples
sqlpostgresqlunpivot

Convert columns data into rows in PostgreSQL

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?

Solution

  • 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