PostgreSQL Crosstab with dynamic column names
<p>I have the following code to generate a pivot table:</p>

<pre><code>CREATE EXTENSION tablefunc;

CREATE TABLE ct(id serial, identifier varchar, timeframe varchar, value integer);
INSERT INTO ct(identifier, timeframe, value) VALUES('Conversions', '16/01', 3);
INSERT INTO ct(identifier, timeframe, value) VALUES('Conversions', '16/02', 5);
INSERT INTO ct(identifier, timeframe, value) VALUES('Conversions', '16/03', 2);
INSERT INTO ct(identifier, timeframe, value) VALUES('Quotes', '16/01', 8);
INSERT INTO ct(identifier, timeframe, value) VALUES('Quotes', '16/02', 12);
INSERT INTO ct(identifier, timeframe, value) VALUES('Quotes', '16/03', 15);

SELECT *
FROM crosstab('SELECT identifier, timeframe, value
          FROM ct
          ORDER BY 1, 2', 'SELECT to_char(generate_series,''YY/MM'') tr FROM generate_series(''2016-01-01'', ''2016-04-13'', ''1 month''::interval) ORDER BY 1')
AS ct(row_name varchar, m1 integer, m2 integer, m3 integer, m4 integer);
</code></pre>

<p>The result is:</p>

<pre><code>row_name    | m1 | m2 | m3 | m4
Conversions | 3  | 5  | 2  |
Quotes      | 8  | 12 | 15 |
</code></pre>

<p>The Function "generate_series_year_month" is of no importance so far, it just delivers a series of months in format YY/MM given a start date and end date. It's only used to make the crosstab function work correctly when I don't have a row for each identifiert in each month.</p>

<p>My question is: How can I dynamically create the column names on the output  without having to specify them explicitly with m1, m2, m3, m4?</p>

<p>The desired column names shoud look like this:</p>

<pre><code>row_name    | 16/01 | 16/02 | 16/03 | 16/04
Conversions | 3     | 5     | 2     | 
Quotes      | 8     | 12    | 15    |
</code></pre>

<p>They should come from the generate_series_year_month function and not from table ct (since there might not be a row for every month in it).</p>