How to unpivot a table in Google Sheets easily

An easy trick that I discovered while trying to unpivot a table in google sheets was to use the array literal syntax. This syntax is close to how some programming languages deal with arrays and matrices. The only requirement to use this syntax in your google sheet is to have a manageable amount of columns that you can type manually in your formula.

First thing, here’s an example of a formula that allows you to unpivot a table:

={
QUERY(all, "select A, B, 'Col1' label B 'Value', 'Col1' 'Column'", 1);
QUERY(all, "select A, C, 'Col2' offset 1 label 'Col2' ''", 0);
QUERY(all, "select A, D, 'Col3' offset 1 label 'Col3' ''", 0)
}

This unpivots this table:

| ID | Col1 | Col2 | Col3 |
|----|------|------|------|
| 1  | 10   | 100  | 1000 |
| 2  | 20   | 200  | 2000 |
| 3  | 30   | 300  | 3000 |

Into this table:

| ID | Value | Column |
|----|-------|--------|
| 1  | 10    | Col1   |
| 2  | 20    | Col1   |
| 3  | 30    | Col1   |
| 1  | 100   | Col2   |
| 2  | 200   | Col2   |
| 3  | 300   | Col2   |
| 1  | 1000  | Col3   |
| 2  | 2000  | Col3   |
| 3  | 3000  | Col3   |

What really happens here is just appending each column individually to a growing list of rows. This syntax is the array literal syntax in google sheets{}…