Home > Net >  Google Sheets query pivot multiple values
Google Sheets query pivot multiple values

Time:02-11

I am trying to use a Query to replicate the behaviour pictured below. I have a pivot table with two values (as rows) displaying a job name and it's status. This works fine with the built-in pivot table.

Sample Data: Sample data

Resulting pivot table: Pivot table

I've then tried to recreate this using the following Query formula:

=QUERY(A1:D6, " select C, max(A), max(D) GROUP BY C PIVOT B Order by C ")

This gives me the result below. This is as close as I've managed to get to replicating the original pivot table.

Query

Edit

The main part of this question has been solved below but I'm wondering how to extend this out if I had more columns of data that I then wanted to display in the same way. Below I have added an additional column 'Other'.

New data: New data

New desired result: enter image description here

CodePudding user response:

Try:

=Index({"",Transpose(Unique(Filter(B2:B,B2:B<>"")));Flatten({Sort(Unique(C2:C)),IFError(Unique(C2:C)/0)}),IFNA(VLookup(Transpose(Unique(Filter(B2:B,B2:B<>"")))&Flatten({Text(Sort(Unique(C2:C)),"hh:mm"),Text(Sort(Unique(C2:C)),"hh:mm")&".1"}),{Flatten({B2:B&Text(C2:C,"hh:mm"),B2:B&Text(C2:C,"hh:mm")&".1"}),Flatten({A2:A,D2:D})},2,0))})

enter image description here

  • Related