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, 2) 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 |
Below is generic/dynamic way to handle your case
EXECUTE IMMEDIATE (
SELECT """
SELECT id, """ ||
STRING_AGG("""MAX(IF(name = '""" || name || """', score, NULL)) AS """ || name, ', ')
|| """
FROM `project.dataset.table` t, t.test
GROUP BY id
"""
FROM (
SELECT DISTINCT name
FROM `project.dataset.table` t, t.test
ORDER BY name
)
);
If to apply to sample data from your question - output is
Row id a b c
1 1 5 7 null
2 2 8 null 3