CODEWITHSUNDEEP
google-sheetspivot-tableformula
Michel
Michel

Reputation: 65

Google Sheets : simple query with pivot clause

I can't find a way to simplify a query to get a similar result to a pivot. Although a pivot table is easy, I would prefer to use a query because I can then personalise headers.

Here's the link with the data :

https://docs.google.com/spreadsheets/d/1R461nv2lD4efpuUuDonFMYGnLH510n7gwpbzG2WAUaw/edit?usp=sharing.

This is the working embedded queries :

= query(query(Data!A1:C, " SELECT B,count(B) WHERE B is not null GROUP BY B,C ORDER BY B ASC "), "SELECT Col1, Count(Col1) group by Col1 LABEL Col1 'Status',Count(Col1) 'Count'",1 )

This is the query I'd like to use with a pivot clause :

=query(Data!A1:C, "Select B, count(C) group by count(C)  Pivot C" ,1)

Here's what I'm looking for : enter image description here

Upvotes: 0

Views: 653

Answers (2)

Mike Steelson
Mike Steelson

Reputation: 15328

Try

={"Status","Count(Categories)";transpose(query(unique(B:C),"select count(Col2) where Col2 is not null pivot Col1",1))}

enter image description here

or simplier

=query(unique(B:C),"select Col1, count(Col2) where Col2 is not null group by Col1",1)

enter image description here

Upvotes: 2

player0
player0

Reputation: 1

if you want to pivot C column it should be:

=QUERY({Data!A1:C, Data!C1:C}, 
 "select Col2,count(Col3) where Col1 is not null group by Col2 pivot Col4", 1)

enter image description here

Upvotes: 0

Related Questions