I have below table

CREATE TABLE bar (project,month,hours)
    ('A', 'aug', 1 ),
    ('A', 'sep', 3 ),
    ('B', 'aug', 2 ),
    ('B', 'sep', 5 );

I can crosstab this with month as category as below

select * from crosstab (
'select project, month, hours from bar',
'select distinct month from bar'
) as 
c (project text, aug int, sep int);

The problem is month column can be vary. it can be vary from number or names.

Ex: Next time month column in bar table can have oct also which I don't know beforehand.

Then How I create crosstab query for that? In summary what I want is to remove hardcoded aug, sep from my crosstab query.

marked as duplicate by Evan Carroll, RDFozz, Erik Darling, John Eisbrener, Vérace Dec 8 '17 at 19:34

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.


Seeing as you might be querying all months at some time, you could just add all the months to the crosstab query beforehand.

select * from crosstab 
    'select project, month, hours from bar order by 1,2',
    $$SELECT unnest('{jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec}'::text[])$$
    ) as  c 
    "project text" text, 
    "jan" int, "feb" int, "mar" int, "apr" int, 
    "may" int, "jun" int, "jul" int, "aug" int, 
    "sep" int, "oct" int, "nov" int, "dec" int

This solution is based on the answer to the question PostgreSQL Crosstab Query on stackoverflow.com.

Not the answer you're looking for? Browse other questions tagged or ask your own question.