BigQuery - Pivot with dinamic values, but I get error "Generating an implicit alias for this PIVOT..

[alexsp]

I spent some time to do a - I think - simply dinamic pivot on BigQuery (the columns will be added dynamicaly), but I could find a solution.

A piece of my code is like:

declare dec string;
set dec = (
select
string_agg (distinct concat("'",extract (year from date1), '-',
extract (month from date1), "'" )) as ref_active_cte
from` table
);

execute immediate
"""

with cte_block1 as (), -- does not really matter
cte_block2 as () -- -- does not really matter

select *
from (
select value, classes, year
)
pivot(sum(value) for classes in (?) )
"""
using dec

I just need a simple template code that I can use to make a dynamic pivot (columns added dynamically) , because I will have many months or maybe classes. And I would like to know what I am doing wrong in my code.