Google Sheets query pivot multiple values

Question

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:

Resulting 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.

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 desired result:

Is there any issue with the pivot table? I think that's a better approach rather than using a query, due to the calculated columns with combined data, I wouldn't do it, but maybe your use case requires it — David Salomon
– David Salomon, Commented Feb 8, 2022 at 1:37
sadly i need a little more flexibility than pivot tables seem to offer. — herman
– herman, Commented Feb 8, 2022 at 21:13

z.. · Accepted Answer · 2022-02-08 01:31:22Z

1

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))})

answered Feb 8, 2022 at 1:31

z..

14k2 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

herman Over a year ago

Interesting! Can confirm that this does indeed work. Will have to take some time to digest what is going on here now. Thanks

herman Over a year ago

Curious to know if there is a way to do this with more columns of data, like in my edit to the original post?

player0 Over a year ago

@herman yes: stackoverflow.com/a/71080343/5632629

Collectives™ on Stack Overflow

Google Sheets query pivot multiple values

Edit

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Edit

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related