title: How to make crosstab in PostgreSQL?
body: <p>CREATE TEMPORARY TABLE</p>
  
  <pre><code>CREATE TEMP TABLE pivot(
  gid SERIAL,
  zoom smallint NOT NULL,
  day timestamp with time zone NOT NULL,
  point integer NOT NULL
  );
  </code></pre>
  
  <p>INSERT DATA</p>
  
  <pre><code>INSERT INTO pivot(zoom, day, point) VALUES(6,'2015-10-01',21);
  INSERT INTO pivot(zoom, day, point) VALUES(7,'2015-10-01',43);
  INSERT INTO pivot(zoom, day, point) VALUES(8,'2015-10-01',18);
  INSERT INTO pivot(zoom, day, point) VALUES(9,'2015-10-01',14);
  INSERT INTO pivot(zoom, day, point) VALUES(10,'2015-10-01',23);
  INSERT INTO pivot(zoom, day, point) VALUES(11,'2015-10-01',54);
  INSERT INTO pivot(zoom, day, point) VALUES(6,'2015-10-02',657);
  INSERT INTO pivot(zoom, day, point) VALUES(7,'2015-10-02',432);
  INSERT INTO pivot(zoom, day, point) VALUES(8,'2015-10-02',421);
  INSERT INTO pivot(zoom, day, point) VALUES(9,'2015-10-02',432);
  INSERT INTO pivot(zoom, day, point) VALUES(10,'2015-10-02',454);
  INSERT INTO pivot(zoom, day, point) VALUES(11,'2015-10-02',654);
  </code></pre>
  
  <p>Lets see if everything works until now:</p>
  
  <pre><code>SELECT zoom, day, point
  FROM   pivot
  ORDER  BY 1,2;
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code> zoom |          day           | point
  ------+------------------------+-------
      6 | 2015-10-01 00:00:00+02 |    21
      6 | 2015-10-02 00:00:00+02 |   657
      7 | 2015-10-01 00:00:00+02 |    43
      7 | 2015-10-02 00:00:00+02 |   432
      8 | 2015-10-01 00:00:00+02 |    18
      8 | 2015-10-02 00:00:00+02 |   421
      9 | 2015-10-01 00:00:00+02 |    14
      9 | 2015-10-02 00:00:00+02 |   432
     10 | 2015-10-01 00:00:00+02 |    23
     10 | 2015-10-02 00:00:00+02 |   454
     11 | 2015-10-01 00:00:00+02 |    54
     11 | 2015-10-02 00:00:00+02 |   654
  (12 rows)
  </code></pre>
  
  <p>CREATE EXTENSION:</p>
  
  <pre><code>CREATE EXTENSION tablefunc;
  </code></pre>
  
  <p>CROSSTAB QUERY</p>
  
  <pre><code>SELECT * FROM crosstab(
         'SELECT zoom, day, point
          FROM   pivot
          ORDER  BY 1,2'
        ,$$VALUES ('2015-10-01'::timestamp), ('2015-10-02')$$)
  AS ct ("zoom" smallint, "2015-10-01" integer, "2015-10-02" integer);
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code> zoom | 2015-10-01 | 2015-10-02
  ------+------------+------------
      6 |            |
      7 |            |
      8 |            |
      9 |            |
     10 |            |
     11 |            |
  (6 rows)
  </code></pre>
  
  <p>I cannot return values of the points, the query itself gives me an empty spots. What am I doing wrong?</p>
  
  <p><strong>Edit</strong></p>
  
  <p>I tried to do it the other way, but I am still searching for the answer to the question above.</p>
  
  <pre><code>SELECT  * from crosstab (
      'select zoom, day, point
       from pivot
       order by 1,2',
      'select distinct day from pivot order by 1')
  AS ct(zoom smallint, "2015-10-01" integer, "2015-10-02" integer);
  </code></pre>
  
  <p>Result:</p>
  
  <pre><code> zoom | 2015-10-01 | 2015-10-02
  ------+------------+------------
      6 |         21 |        657
      7 |         43 |        432
      8 |         18 |        421
      9 |         14 |        432
     10 |         23 |        454
     11 |         54 |        654
  (6 rows)
  </code></pre>