Postgres, split single row into multiple rows

Question

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.

"Is there a way to do this without the mess?" Yes, it is called normalize your data. — Belayer, Nov 04 '20 at 20:14
The source data is from the CDC, its aggregated into rows in this unfortunate way, we are actually normalizing the data with the very project related to this question, thanks for your helpful (?) input. — JP Silvashy, Nov 04 '20 at 21:16

score 2 · Accepted Answer · answered Nov 04 '20 at 15:21

2

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)

answered Nov 04 '20 at 15:21

GMB

216,147
25
84
135

1

Thanks, this worked great, learning about lateral joins now. – JP Silvashy Nov 04 '20 at 16:26

Postgres, split single row into multiple rows

1 Answers1