postgres crosstab with unknown number of categories

Question

I have below table

CREATE TABLE bar (project,month,hours)
    AS VALUES
    ('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.

score 0 · Answer 1 · answered Dec 08 '17 at 09:30

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.

postgres crosstab with unknown number of categories

1 Answers1