Hey I am trying to transform a table from this:

enter image description here

Into this:

enter image description here

Basically for each "Type" I want the totals for the different values for "Exists" which right now is just TRUE and FALSE.

But I can only get to this so far:

enter image description here

I did that using the following QUERY


I know the next step requires a PIVOT but I'm having some fundamental misunderstanding about how PIVOT works to get anywhere. If you could help me out with some understanding and not just give me the answer it would be great.

1 Answer 1


The select argument of QUERY doesn't allow to use the same column to group, aggregate and to pivot data.

The workaround is to add an auxiliary column that repeats the data from C, let say it will be D, the select argument will be

  • Sorry, this answer doesn't get me to the second picture in my post which is where I'm trying to go. May 12, 2018 at 20:45
  • @BarryRogerson I made a "slight" change to my answer May 12, 2018 at 21:00
  • thanks for the update; that works now. So is there an elegant way to make "D" on the fly in the query or must it be done quite manually ? My actual problem, for which this is a simplification, has many sorts of "Exists" columns so it would be bad to have do duplicate each one. May 12, 2018 at 21:56
  • 1
    @BarryRogerson you could use an "embedded/inline array" -> webapps.stackexchange.com/a/80611/88163. Bear in mind that instead of Col1 notation should be used instead of A notation for column names -> webapps.stackexchange.com/q/91919/88163 May 12, 2018 at 21:59
  • 1
    @DavidLeal From the answer: "the workaround is to add an auxiliary column that repeats the data from C, let say it will be D" Jul 17, 2021 at 5:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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