Transpose multiple columns into single column

Question

I have a Google sheet in which the rows are unique cities and the columns are years. It can grow in two dimensions.

|            |         2010         |          2011        |
-----------------------------------------------------------
|    City    | Temp low | Temp high | Temp low | Temp high |
-----------------------------------------------------------
| London     |    10    |     11    |    21    |     22    |
| Birmingham |    12    |     13    |    23    |     24    |

However, this double header doesn't work for creating a pivot table.

How can I transform the table such that it grows only in the row dimension and the years are simply a value in a single column?

|    City    | Year | Temp low | Temp high |
-------------------------------------------
| London     | 2010 |    10    |     11    |
| Birmingham | 2010 |    12    |     13    |
| London     | 2011 |    21    |     22    |
| Birmingham | 2011 |    23    |     24    |

Here is a sample sheet of the above example.

The formula shouldn't need to be adapted when new rows or columns are added. However, the column pattern will remain the same and can be relied on.

Answer 1

Please use the following formula

=QUERY({{"Year";ArrayFormula(TRANSPOSE(SPLIT(JOIN(" ",REPT(B1:E1&" ",COUNTA(A3:A)))," ")))}, 
              query({A2:A,B2:C;A3:A,D3:E}, 
                     "where Col1 is not null",1)}, 
          "select Col2, Col1, Col3, Col4 order by Col1, Col2",1)

If you want to order by city and then by year use

=QUERY({{"Year";ArrayFormula(TRANSPOSE(SPLIT(JOIN(" ",REPT(B1:E1&" ",COUNTA(A3:A)))," ")))}, 
              query({A2:A,B2:C;A3:A,D3:E}, 
                     "where Col1 is not null",1)}, 
          "select Col2, Col1, Col3, Col4 order by Col2, Col1",1)

(The above formula is actually a modified version of this answer. Please refer to it for more info and how the formula works.)

Answer 2

So, turns out, the general problem is called "Unpivoting". Unfortunately there is no function in Google Sheets yet.

I found the following answers useful.

Manual

UNPIVOT function in Google Sheets

How do you create a “reverse pivot” in Google Sheets?

Using Script

How do you create a “reverse pivot” in Google Sheets?

Other

Transpose Ranges repeated by row

Converting multiple columns to list

Analyse multiple columns per row

Split columns in to rows in google spread sheets

Transpose names across multiple columns/rows into a single column in Google Sheet with twist