Google Sheets - Query Pivot - show all results

Question

In google sheets have a pivot table with columns with text day ranges 1-30, 31-60, 61-90, 90-120, >120 where some records fall under those day ranges.

This is sample data:

Unique	Account Doc	Amount	Day Range
1	123456	1000	1-30
2	561530	2000	>120
3	123456	1500	61-90
4	25106	3000	1-30

I can get this data to pivot using standard pivot tables but users needs it to be clean without the pivot table buttons and formatting. I am trying to convert to google query function but I am stumped. I'd could try the option of pivoting and then calling the pivot to a query to remove the formatting but that seems redundant and there is a lot of other things happening in my sheet so afraid of making updating slow.

End result would look like below where the day ranges are pivoted and amount is showing for each record.

All columns need to be preserved as if there are null values all results appear even null/zero values for Amount as with column 31-60 and 91-120 showing the columns with no results. I use a unique id to ensure that all records come back as some of the

I can get the query to pivot with:

=query(rawdata,"Select Z,B,E,D,F,H,J, Sum(N) where B="&$C$31&" Group by Z,B,E,D,F,H,J pivot AA order by F",1)

However if the filter on B only has some day ranges and not others it will only show those columns with data.

Link to google sheet with sample data:

https://docs.google.com/spreadsheets/d/1seX4T3M8Mo9eVZYteyAbUG2zmWM9VCZ2-6oDd76QMA8/edit?usp=sharing

Result Query:

Unique	Account Doc	1-30	31-60	61-90	91-120	>120
1	123456	1000
2	561530					2000
3	123456			1500
4	25106	3000

Answer 1

try:

=ARRAYFORMULA(QUERY(QUERY({'Copy of raw data'!A:AA; IFERROR(VLOOKUP(
 SEQUENCE(COUNTA(UNIQUE('Copy of raw data'!B2:B)&TRANSPOSE(UNIQUE('Copy of raw data'!AA2:AA)))), {
 SEQUENCE(COUNTA(UNIQUE('Copy of raw data'!B2:B)&TRANSPOSE(UNIQUE('Copy of raw data'!AA2:AA)))), 
 SPLIT(FLATTEN(UNIQUE('Copy of raw data'!B2:B)&"×"&TRANSPOSE(UNIQUE('Copy of raw data'!AA2:AA))), "×"), 
 TO_TEXT(SPLIT(FLATTEN(UNIQUE('Copy of raw data'!B2:B)&"×"&TRANSPOSE(UNIQUE('Copy of raw data'!AA2:AA))), "×"))}, 
 {0, 2, SEQUENCE(1, 24, 0, 0), 5}, 0))},
 "select Col26,Col2,Col5,Col4,Col6,Col8,Col10,sum(Col14) 
  where 1=1 "&IF(B1="",, " and Col2="&B1)&"
  group by Col26,Col2,Col5,Col4,Col6,Col8,Col10 
  pivot Col27 
  order by Col6", 1), "where Col1 is not null", 1))