Postgres generate series of columns

Question

I have a couple of tables

Table 1:

meter_id  |    date 
    1       2019-01-01

Table 2:

meter_id  |  read_date   |  period   |  read   
   1         2019-01-01       1          5
   1         2019-01-01       2          6
   1         2019-01-01       5          2
   1         2019-01-01       6          1
   1         2019-01-01       7          2
   2         2019-01-01       1          3
   2         2019-01-01       2          10
   1         2019-01-02       6          7

Is it possible to generate a series of columns so I end up with something like this:

meter_id  |  read_date  |  p_1  |  p_2   |  p_3  |  p_4  |  p_5  |  p_6   ...
   1         2019-01-01     5       6                        2       1
   2         2019-01-01     3       10     
   1         2019-01-02                                              7

where there are 48 reads per day (every half hour)

Without having to do multiple select statements?

Does this answer your question? Create a pivot table with PostgreSQL — Roman-Stop RU aggression in UA
– Roman-Stop RU aggression in UA, Commented Nov 5, 2019 at 22:42

Gordon Linoff · Accepted Answer · 2019-11-05 23:50:29Z

2

You can use conditional aggregation:

select t1.meter_id, t1.date,
       max(t2.read) filter (where period = 1) as p_1,
       max(t2.read) filter (where period = 2) as p_2,
       max(t2.read) filter (where period = 3) as p_3,
       . . .
from table1 t1 join
     table2 t2
     on t1.meter_id = t2.meter_id and t1.date = t2.read_date
group by t1.meter_id, t1.date;

answered Nov 5, 2019 at 23:50

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Postgres generate series of columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related