Reformat table so headers are values

Question

I have a table in Google BigQuery like this:

| name     | field1   | field2   | field3 |
| -------- | -------- |:--------:| -----: |
| Tim      | 1        | 1        | 1      |
| Todd     | 1        | 2        | 2      |
| Fred     | 2        | 2        | 1      |

A system I'm working with needs the headers as a value in a column named 'field_name', and the values added to a column named 'value' like this:

| name    | field_name | value |
| ------- | ---------- |:-----:|
| Tim     | field1     | 1     |
| Tim     | field1     | 1     |
| Tim     | field1     | 2     |
| Todd    | field2     | 1     |
| Todd    | field2     | 2     |
| Todd    | field2     | 2     |
| Fred    | field3     | 1     |
| Fred    | field3     | 2     |
| Fred    | field3     | 1     |

Is this possible to do with SQL? I can imagine how to do this in python but am struggling to see how this could be done with SQL.

Answer 1

Use union all:

select name, 'field1' as fieldname, field1 as value from mytable
union all select name, 'field2', field2 from mytable
union all select name, 'field3', field3 from mytable