CODEWITHSUNDEEP
sqlgoogle-sheetspivot
DHolcomb
DHolcomb

Reputation: 71

How to use a google sheets pivot query to output strings

I have a (much larger) table like this sample:

Original Data

I am trying to output a table that looks like this:

Desired Outcome

The closest I can get with a pivot query returns numerical results in the value fields, rather than the desired text strings

=query(Data, "Select D,count(D) group by D Pivot B")

I resorted to a series of formulas to build my row and column headers, and then fill in the data field - See Version 3 in the sample sheet. But I couldn't figure out how to fill in the data with a single formula - as opposed to copying and pasting in the data field, which is not desirable with a dynamic number of row and column headers based on the original data.

Is there a way to wrap my data field formula (in cell B44 of the sample) in an arrayformula that will fill the data field, with a dynamic number of columns and rows?

Or even more advanced is there a formula that will deliver my desired results table in a single formula?

Upvotes: 1

Views: 972

Answers (1)

MattKing
MattKing

Reputation: 7783

This should work, it's a bit difficult to explain, but i could demonstrate the various parts if you opened up your sheet to editable...

=ARRAYFORMULA(TRANSPOSE(QUERY(TRIM(SPLIT(TRANSPOSE(QUERY(QUERY({CHAR(10)&A2:A11,B2:B11&"|"&D2:D11&"|"},"select MAX(Col1) group by Col1 pivot Col2"),,9^9)),"|",0,0)),"select Col1,MAX(Col3) where Col1<>'' group by Col1 pivot Col2 order by Col1 desc label Col1'Project'")))

Upvotes: 2

Related Questions