Say I have a BQ table containing the following information

| id | test.name | test.score |
| --- | --- | --- |
| 1 | a | 5 |
|  | b | 7 |
| 2 | a | 8 |
|  | c | 3 |

Where test is nested. How would I pivot test into the following table?

| id | a | b | c |
| --- | --- | --- | --- |
| 1 | 5 | 7 |  |
| 2 | 8 |  | 3 |

I cannot pivot test directly, as I get the following error message at `pivot(test)`: `Table-valued function not found`. Previous questions ([1](https://stackoverflow.com/questions/26272514/how-to-pivot-table-in-bigquery), [2](https://stackoverflow.com/questions/40761453/transpose-rows-into-columns-in-bigquery-pivot-implementation)) don't deal with nested columns or are outdated.

The following query looks like a useful first step:

```
select a.id, t
from `table` as a,
unnest(test) as t

```

However, this just provides me with:

| id | test.name | test.score |
| --- | --- | --- |
| 1 | a | 5 |
| 1 | b | 7 |
| 2 | a | 8 |
| 2 | c | 3 |