query: Remove aggregate function name for header-CodePudding

I have simulated joining in google sheets with one =query function per row. If I put an aggregate function in there, this does not work anymore, since I'll always get two rows at least: 1 for displaying what aggegate function I have, one displaying the result:

=QUERY(Departments!B:N;"select N, max(C),max(M), max(J) where N="&A4&" group by N";0)

results in

            max         max     max 
   21001    27.11.2022  20000   40000

even though I have the "headers" set to 0.

this is probably a bug in google sheets - but is there another way to get rid of the max's row?

CodePudding user response：

use:

=QUERY(QUERY(Departments!B:N;
 "select N,max(C),max(M),max(J) 
  where N="&A4&" 
  group by N"; ); "offset 1"; )

or:

=QUERY(Departments!B:N;
 "select N,max(C),max(M),max(J) 
  where N="&A4&" 
  group by N 
  label max(C)'',max(M)'',max(J)''"; )

CodePudding user response：

=QUERY(Departments!B:N; "select N, max(C) as MaxC, max(M) as MaxM, max(J) as MaxJ where N="&A4&" group by N"; 0)

=PIVOT(Departments!B:N; N; C, M, J; "MaxC", "MaxM", "MaxJ")