Google sheets pivot table: show items with no data

Question

I am trying to find the correct google query syntax to show a pivot column for every row element that exists in the source data, even if there is no data for it after the 'where' clause has been applied. This way, the resultant pivot table will always be the same number of columns wide, making it possible to perform further computations on the data based on column.

This is the equivalent of creating a pivot table in Microsoft Excel and selecting the 'show items with no data' option.

Here is the current code:

=query(Memberships!A:P,"SELECT E,COUNT(H) where J >=1 group by E PIVOT B")

What should the code be to produce the result that I want?

I could use either a query statement, or the Google Sheets pivot functionality if it has a way to do this.

Answer 1

If you want to include the rows that are excluded by WHERE clause, then it's necessary to use something other than WHERE. A typical workaround is to add a column with arrayformula(N(J:J>=1)) which will have 1 in the rows that satisfy the condition, and 0 for others. The select sum() of that column in place of count(). The effect is that zero counts are returned as 0; so the corresponding rows are preserved and are used in pivot.

The extra column can be added on the fly, for example

=query({A2:J, N(J2:J>=1)}, "select Col5, sum(Col11) group by Col5 pivot Col2")

where the columns are now referred to by their positions in the array {A2:J, N(J2:J>=1)}: Col5 is E, Col2 is B, and Col11 is the extra column at the end.

One may find the presence of zeros undesirable. They can be filtered out with the If condition, though this more than doubles the length of the formula:

=arrayformula(if(query({A2:J,N(J2:J>=1)}, "select Col5, sum(Col11) group by Col5 pivot Col2") = 0, , query({A2:J,N(J2:J>=1)}, "select Col5, sum(Col11) group by Col5 pivot Col2"))) 

The second argument of if is intentionally left blank.