title: Postgres Dynamic Pivot Table
body: <p>I am needing to create this table (not really for donuts).</p>
  
  <p>I basically want to output the time to sell a product within a certain time frame.</p>
  
  <p>The time frame is defined in a table and needs to be able to be reconfigured (dropped and recreated) without breaking the query.</p>
  
  <p>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.</p>
  
  <p><a href="http://sqlfiddle.com/#!1/a8309/1" rel="nofollow">SQLFiddle</a></p>
  
  <pre><code>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  |
  --  +---------+-------------+----------------+----------------+----------------+
  </code></pre>