PostgreSQL Subquery – Subquery for Column Names in Tablefunc Crosstab Queries

pivotpostgresql

Is it possible to get the values for the names of the columns for the crosstab query from a subquery?

CREATE TABLE mytable(employee_name text, month date, allocation double precision);
INSERT INTO mytable VALUES('A','2017-11-30','0.5');
INSERT INTO mytable VALUES('B','2017-11-30','0.8');
INSERT INTO mytable VALUES('B','2017-12-31','0.5');


SELECT * FROM crosstab
(
  'SELECT employee_name , month , allocation FROM mytable ORDER BY 1',
  'SELECT DISTINCT month FROM mytable ORDER BY 1'
)
AS
(
    employee_name text  
    # Here a subquery to get column names
    # automatically eg : 'SELECT DISTINCT month FROM mytable'
);

Best Answer

No, you can't make your resultset's schema dependent on the values inside the table. But you can use dynamic sql to generate a query.

SELECT FORMAT($$

  SELECT * FROM crosstab
  (
    'SELECT employee_name , month , allocation FROM mytable ORDER BY 1',
    'SELECT DISTINCT month FROM mytable ORDER BY 1'
  )
  AS
  (
    employee_name text,
    %s
  )
$$,
  'something here...'
);

Now you just want to fill out that 'something here..'

SELECT FORMAT(
    $$
        SELECT * FROM crosstab
        (
            'SELECT employee_name , month , allocation
             FROM mytable
            ORDER BY 1, 2',
            'SELECT DISTINCT month FROM mytable ORDER BY 1'
        )
        AS
        (
            employee_name text,
            %s
        )
    $$,
    string_agg(
        FORMAT('%I %s', month, 'double precision'), ', '
        ORDER BY month
    )
)
FROM (
    SELECT DISTINCT month
    FROM mytable
)
    AS t;

Then run the query that produces, and you get..

 employee_name | 2017-11-30 | 2017-12-31 
---------------+------------+------------
 A             |        0.5 |           
 B             |        0.8 |        0.5
(2 rows)
Related Question