How I can flatten a count table in postgresql that contains only 2 columns with crosstab?
Use conditional aggregation:
select count(*) filter (where category = 'category1') as category1,
       count(*) filter (where category = 'category2') as category2,
       count(*) filter (where category = 'category3') as category3
from the_table;