Table transformation Google Sheets

Question

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!

Answer 1

use:

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