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?

user330315 · Accepted Answer · 2018-07-18 11:32:19Z

5

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

answered Jul 18, 2018 at 11:32

user330315

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

Comments

Tim Biegeleisen · Accepted Answer · 2018-07-18 11:27:10Z

0

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.

answered Jul 18, 2018 at 11:27

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

Gaj · Accepted Answer · 2018-07-18 11:30:30Z

0

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

answered Jul 18, 2018 at 11:30

Gaj

8865 silver badges5 bronze badges

Collectives™ on Stack Overflow

Convert columns data into rows in PostgreSQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related