CODEWITHSUNDEEP
sqlpostgresqlsubqueryunpivotlateral-join
JP Silvashy
JP Silvashy

Reputation: 48535

Postgres, split single row into multiple rows

I've got a table with columns like:

+-------+------+-------+-------+-------+-------+
| age1  | val1 | age2  | val2  | age3  | val3  |
+-------+------+-------+-------+-------+-------+
| 30-40 | 34.5 | 41-50 | 32.01 | 51-60 | 29.13 |
+-------+------+-------+-------+-------+-------+

I want to return:

+-------+-------+
|  age  |  val  |
+-------+-------+
| 30-40 |  34.5 |
| 41-50 | 32.01 |
| 51-60 | 29.13 |
+-------+-------+

I ended out writing a large query using 3 unions like this, but this does not seem right:

  select *
  from ( ... ) g1
  union
  select *
  from ( ... ) g2
  union
  select *
  from ( ... ) g3

Is there a way to do this without the mess? Seems like I'm missing something really obvious.

Upvotes: 0

Views: 2018

Answers (1)

GMB
GMB

Reputation: 222622

In Postgres, you can efficiently unpivot the columns to rows with a lateral join:

select x.*
from mytable as t
cross join lateral (values 
    (t.age1, t.val1), 
    (t.age2, t.val2), 
    (t.age3, t.val3)
) as x(age, val)

Upvotes: 2

Related Questions