Postgres Dynamic Pivot Table

Question

I am needing to create this table (not really for donuts).

I basically want to output the time to sell a product within a certain time frame.

The time frame is defined in a table and needs to be able to be reconfigured (dropped and recreated) without breaking the query.

That is to say the time_frames we are interested in monitoring will, by design, change often. Today there may be 4 timeframes (6h ea) defined, tomorrow I might make just one (24h), the next day there may be 12 rows(2 ea). The time_frames might also be of differing lengths. The columns would stay the same.

SQLFiddle

CREATE TABLE time_frame 
  (
    id SERIAL NOT NULL
    ,start_time time
    ,end_time time
    ,PRIMARY KEY(id)
  );

CREATE TABLE donut_sales 
  (
    saleid SERIAL NOT NULL
    ,donutid INT
    ,donutname TEXT
    ,stocked timestamp
    ,sold timestamp
    ,PRIMARY KEY (saleid)
  );

--  SELECT * FROM FANCY_PIVOT_TABLE_I_DONT_UNDERSTAND
--  +---------+-------------+----------------+----------------+----------------+
--  | donutid | donutname   |  time_frame_1  |  time_frame_2  |  time_frame_3  |
--  +---------+-------------+----------------+----------------+----------------+
--  |       1 | sprinkles   |      00:17:66  |      00:17:66  |      00:17:66  |
--  |       2 | jelly       |      00:17:66  |      00:17:66  |      00:17:66  |
--  |       3 | custard     |      00:17:66  |      00:17:66  |      00:17:66  |
--  +---------+-------------+----------------+----------------+----------------+

"That is to say the time_frames we are interested in monitoring will, by design, change often." you mean new time_frames rows are added or does the table structure change.. "The time frame is defined in a table and needs to be able to be reconfigured (dropped and recreated) without breaking the query." with the same structure? — Raymond Nijland, Aug 31 '13 at 11:28
The structure would stay the same but I might drop and recreate the rows with all new values. Today there could be four rows, and tomorrow there could be 12. — gooddadmike, Sep 02 '13 at 13:54

score 1 · Answer 1 · answered Aug 31 '13 at 10:21

I actually don't understand how time values appear in your output table, but if you want to count number of donuts sold in each time_frame, you can use this query:

select
    ds.donutid, ds.donutname,
    sum(case when ts.id = 1 then 1 else 0 end) as time_frame_1,
    sum(case when ts.id = 2 then 2 else 0 end) as time_frame_2,
    sum(case when ts.id = 3 then 3 else 0 end) as time_frame_3,
    sum(case when ts.id = 4 then 4 else 0 end) as time_frame_4
from donut_sales as ds
    inner join time_frame as ts on
        ts.start_time <= ds.sold::time and ts.end_time > ds.sold::time
group by ds.donutid, ds.donutname
order by ds.donutid

sql fiddle demo

You can't make dynamic number of time_frames, though, because you cannot make dynamic columns in output in PostgreSQL.

I was looking specifically for dynamic output. Like this in MSSQL http://stackoverflow.com/questions/11404062/sql-server-advanced-report-of-multiple-rows-into-one — gooddadmike, Sep 10 '13 at 15:24

Postgres Dynamic Pivot Table

1 Answers1