title: PostgreSQL query with dynamic number of columns
body: <p>I am trying to find a method to return a record set with a dynamic number of columns. I can write one query that will produce the list of column names I need as such:</p>
  
  <pre><code>SELECT DISTINCT name FROM tests WHERE group = 'basic';
  </code></pre>
  
  <p>This will return a short list like 'poke', 'prod', 'hit', 'drop', etc. Then I want a table produced showing a series of tests where each of those tests were run. Every morning we look at what the developers have been doing and poke and prod at it so each test will be run for each day. This query I can write statically:</p>
  
  <pre><code>SELECT (SELECT success FROM test_results AS i
          WHERE i.name = 'poke'
          AND i.date = o.date) AS 'poke',
         (SELECT success FROM test_results AS i
          WHERE i.name = 'prod'
          AND i.date = o.date) AS 'prod',
  ...
  FROM test_results AS o GROUP BY date
  HAVING date &gt; now() - '1 week'::interval;
  </code></pre>
  
  <p>However, this is hard-coded to the tests we are running on each day. If we now need to start kicking the device each day, we need to update the query. If we decide the drop test is no longer needed, after a week, the drop test column should drop off the report as it no longer occurs in the results. Returning NULL for missing tests when only certain dates have a results entry is perfectly acceptable.</p>
  
  <p>Is there a method to create a dynamic list of columns from the results by just using regular SQL in a query?</p>
  
  <p>I was attempting to build up the data I need in parts by using a <code>WITH</code> query, but I can't find a way to build up the final row correctly from dynamic information.</p>
  
  <p><strong>Edit:</strong> Here's some sample data from the last two days:</p>
  
  <pre><code>CREATE TABLE test_results (
      name TEXT NOT NULL,
      date DATE default now() NOT NULL,
      success BOOLEAN NOT NULL
  );
  
  INSERT INTO test_results (name, date, success) VALUES ('hit',  '2017-06-20', TRUE);
  INSERT INTO test_results (name, date, success) VALUES ('poke', '2017-06-20', TRUE);
  INSERT INTO test_results (name, date, success) VALUES ('prod', '2017-06-20', TRUE);
  
  INSERT INTO test_results (name, date, success) VALUES ('poke', '2017-06-21', TRUE);
  INSERT INTO test_results (name, date, success) VALUES ('prod', '2017-06-21', TRUE);
  
  INSERT INTO test_results (name, date, success) VALUES ('poke', '2017-06-22', TRUE);
  INSERT INTO test_results (name, date, success) VALUES ('prod', '2017-06-22', FALSE);
  
  INSERT INTO test_results (name, date, success) VALUES ('poke', '2017-06-23', TRUE);
  INSERT INTO test_results (name, date, success) VALUES ('prod', '2017-06-23', TRUE);
  INSERT INTO test_results (name, date, success) VALUES ('drop', '2017-06-23', TRUE);
  </code></pre>
  
  <p>If I run my query against the data range of 2017-06-21 to 2017-06-23, I'd like to get results like the following including a matrix of any tests that were run in that time:</p>
  
  <pre><code>date        | poke   | prod   | drop
  ------------+--------+--------+-----
  2017-06-21  | TRUE   | TRUE   | NULL
  2017-06-22  | TRUE   | FALSE  | NULL
  2017-06-23  | TRUE   | TRUE   | TRUE
  </code></pre>
  
  <p>The names poke, prod, and drop were all names found in the name field of a row during that time period. NULL is returned for the detailed query for any tests that don't have a record for that date.</p>