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?

This is called [pivot table](https://stackoverflow.com/questions/20618323/create-a-pivot-table-with-postgresql) — Roman-Stop RU aggression in UA, Nov 05 '19 at 22:41
Does this answer your question? [Create a pivot table with PostgreSQL](https://stackoverflow.com/questions/20618323/create-a-pivot-table-with-postgresql) — Roman-Stop RU aggression in UA, Nov 05 '19 at 22:42

score 2 · Accepted Answer · answered Nov 05 '19 at 23:50

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;

Postgres generate series of columns

1 Answers1