Problem with range inside QUERY for rebuilding multicolumn table to Col1(key),Col2(values from all other columns)

Question

I want to rebuild my table of Col1 (key), Col2, Col3, Col4, Col5 to table of Col1 (Key), and Col2 (which include all accordance values from Columns 2,3,4,5...), please check example: https://docs.google.com/spreadsheets/d/1je3uc1DHitzzsFK6Xag_ld531p7ozeheO4PwpCfZjQA

If I use the following plain text as range in Query:

=QUERY({A2:A7\B2:B7;A2:A7\C2:C7;A2:A7\D2:D7;A2:A7\E2:E7},"Select * where Col2 >0")

it works well. But I can't make that range to be dynamic inside query, I tried to put range (based on formulas or even as a text) in some cell and then pull it as follows

=QUERY(A1,"Select * where Col >0")

or

=QUERY({indirect(A1)},"Select * where Col2 >0")

but it doesn't work.

Answer 1

You can use this formula where you will need to change only one range in case there will be more columns:

=SORT(SPLIT(QUERY(FLATTEN(IF(B3:E = "",, A3:A & "♥" & B3:E)), "WHERE Col1 IS NOT NULL"), "♥"))

Or you could place A3:A and B3:E ranges in some cells as parameters and use INDIRECT you you need that:

=SORT(SPLIT(QUERY(FLATTEN(IF(INDIRECT(N4) = "",, INDIRECT(N3) & "♥" & INDIRECT(N4))), "WHERE Col1 IS NOT NULL"), "♥"))

Answer 2

When a raw data set may expand, I always recommend that the raw data set remain by itself in a sheet. For now, let's assume that you have only your existing raw data set in the 'Sample' sheet.

In a new blank sheet, place this formula:

=ArrayFormula(QUERY(SPLIT(FLATTEN(FILTER(Sample!A3:A,Sample!A3:A<>"")&"|"&FILTER(FILTER(INDIRECT("Sample!B3:"&ROWS(Sample!A:A)),Sample!A3:A<>""),Sample!B2:2<>"")),"|"),"Select * WHERE Col2 Is Not Null",0))

This formula is similar to what @kishkin offered. However, the FILTER inclusions automatically expand to include all parts of the raw data where there is something in Column A and a header added in B3:B. That is, this formula requires zero maintenance if set up as I've described.