I'm trying to convert such table:

into such table:

First table is very big, and is being changed automatically by another user.

I'd like to use Google Sheets' query function. Any idea how?

Thanks in advance!

CodePudding user response：

use:

=INDEX(QUERY(SPLIT(FLATTEN(B1:D1&"×"&A2:A&"×"&B2:D), "×"), 
 "where Col2 is not null"))