Splitting each row of a table into multiple rows - Google Sheets

Question

Let's say I have a source table in google sheets that looks like this:

| Date       | Item A Quantity | Item A Cost | Item B Quantity | Item B Cost |
|------------|-----------------|-------------|-----------------|-------------|
| 2020-12-27 | 1               | 1.00        | 2               | 3.00        |
| 2020-12-26 | 12              | 1.50        | 5               | 2.00        |

How would I format this so that each row is 'atomic' and describes only a single item (not unlike database normalization)? In this example, it would look like this (the order of rows doesn't matter):

| Date       | Quantity        | Cost        | Item   |
|------------|-----------------|-------------|--------|
| 2020-12-27 | 1               | 1.00        | Item A |
| 2020-12-26 | 12              | 1.50        | Item A |
| 2020-12-27 | 2               | 3.00        | Item B |
| 2020-12-26 | 5               | 2.00        | Item B |

Ideally this needs to be dynamic since the data changes regularly, so I don't think pivot tables will work. I've tried using QUERY, but to no avail so far.

Answer 1

use:

=ARRAYFORMULA(SORT(SPLIT(QUERY(FLATTEN(IF(
 FILTER(B2:G, MOD(COLUMN(B:G)-1, 2)=0)<>"", 
 TO_TEXT(A2:A)&"×"&FILTER(B2:G, MOD(COLUMN(B:G), 2)=0)&"×"&
 FILTER(B2:G, MOD(COLUMN(B:G)-1, 2)=0), )), 
 "where Col1 is not null"), "×"), 2, 0))

---

then:

=ARRAYFORMULA(SORT(SPLIT(QUERY(FLATTEN(IF(
 FILTER(B2:G, MOD(COLUMN(B:G)-1, 2)=0)<>"", 
 TO_TEXT(A2:A)&"×"&FILTER(B2:G, MOD(COLUMN(B:G), 2)=0)&"×"&
 FILTER(B2:G, MOD(COLUMN(B:G)-1, 2)=0)&"×"&REGEXREPLACE(
 FILTER(B1:G1, MOD(COLUMN(B:G)-1, 2)=0), " Quantity| Cost", ), )), 
 "where Col1 is not null"), "×"), 4, 1))