Using crosstab with NULL value columns

user_6282204 0
Viewed 35

I have a script that generates an output like this -

business  name      month   count
home      SPRINGS   April   3
home      SPRINGS   January 31

How do I output this like so -

business  name      Jan  Feb Mar April
home      SPRINGS   31           3
postgresql sql
1 Answers

As the amount of months is finite, I guess you could adjust this script of yours to already deliver the counts in columns:

SELECT DISTINCT ON (business,name)
  business,name,
  count(*) FILTER (WHERE month ='January') AS jan,
  count(*) FILTER (WHERE month ='February') AS feb,
  count(*) FILTER (WHERE month ='March') AS mar,
  count(*) FILTER (WHERE month ='April') AS apr
  .... 
  count(*) FILTER (WHERE month ='December') AS dec
FROM t GROUP BY 1,2;

Demo: db<>fiddle

user_22753880
Related