Combine multiple columns into a single column in Postgres

Question

Trying to write a query that can make the existing Postgres table that looks like first set below to give a result of the second set here:

ID | Month1  | Month2
A  |    2    |   3   --(qty)
B  |    4    |   5   --(qty)

Result

ID  | Month  | QTY
A   | Month1 | 2
A   | Month1 | 3
B   | Month1 | 4
B   | Month1 | 5

The best idea is to use multiple unions but that would be very long. Is there a more efficient way to get around this?

GMB · Accepted Answer · 2020-02-04 22:46:07Z

2

In Postgres, you can unpivot with a lateral join:

select t.id, m.month, m.qty
from mytable t
cross join lateral (values (t.Month1, 'Month1'), (t.Month2, 'Month2')) as m(qty, month)
order by t.id, m.month

Demo on DB Fiddle:

id | month  | qty
:- | :----- | --:
A  | Month1 |   2
A  | Month2 |   3
B  | Month1 |   4
B  | Month2 |   5

answered Feb 4, 2020 at 22:46

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

Hambone · Accepted Answer · 2020-02-05 01:43:12Z

0

Another potential way to do it using generate_series:

select
  f.id,
  'Month' || i as month,
  case gs.i
    when 1 then f.month1
    when 2 then f.month2
  end as month
from
  foo f
  cross join generate_series (1, 2) gs (i)

answered Feb 5, 2020 at 1:43

Hambone

16.5k8 gold badges54 silver badges79 bronze badges

Collectives™ on Stack Overflow

Combine multiple columns into a single column in Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related