2

I have the following code to generate a pivot table:

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);

The result is:

row_name    | m1 | m2 | m3 | m4
Conversions | 3  | 5  | 2  |
Quotes      | 8  | 12 | 15 |

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.

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?

The desired column names shoud look like this:

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

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).

Sebastian
  • 394
  • 1
  • 2
  • 14
  • do you want the adjustable number of columns returned?.. or just custom names?.. – Vao Tsun Nov 29 '16 at 10:58
  • Adjustable number of columns. I plan on putting everything into a function afterwards. So I will have to call it like get_pivot(_startdate_, _enddate_). Depending on the parameter values for _startdate_ and _enddate_, it will return a variable number of columns. E.g. setting them to '2016-01-25' and '2016-03-21' will return 3 columns. – Sebastian Nov 29 '16 at 11:07
  • replace `'SELECT year_month FROM generate_series_year_month(''2016-01-01'',''2016-04-13'') ORDER BY 1'` **with** `'SELECT to_char(generate_series,''YY/MM'') tr FROM generate_series(''2016-01-01'', ''2016-04-13'', ''1 month''::interval) ORDER BY 1'`, so people would easier see what you mean. Or better create the table and function in some playground. Regarding your question, I have a feeling that solution does not exist and workarounds will be very ugly for this task. **if pivot extension would not need clearly defined list of columns, it would not depend on it**, but I'm not good in it, so... – Vao Tsun Nov 29 '16 at 13:57
  • Variable numbers of columns are not supported by SQL so dynamic pivots are achieved with a two-step process: 1) generate dynamic SQL. 2) execute that SQL. See http://stackoverflow.com/questions/36804551/execute-a-dynamic-crosstab-query as one of many already existing answers on this. – Daniel Vérité Dec 01 '16 at 11:17

0 Answers0