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:

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