In BigQuery, when using the PIVOT clause to transform your data, you need to explicitly list the values that you’re pivoting on. BigQuery does not support dynamic pivot values directly.
The error you are getting is because the PIVOT operator in BigQuery does not support implicit aliases. This means that you cannot use a string literal as the value for the FOR clause. Instead, you need to use a variable that contains the list of unique values for the pivot column. Here is a revised version of your code that fixes the error:
DECLARE dec STRING;
SET dec = (SELECT STRING_AGG(DISTINCT CONCAT(“'”, CAST(EXTRACT(YEAR FROM date1) AS STRING), “-”, CAST(EXTRACT(MONTH FROM date1) AS STRING), “'”), ‘,’) FROM table);
EXECUTE IMMEDIATE CONCAT(’
WITH cte_block1 AS (), – does not really matter
cte_block2 AS () – does not really matter
SELECT *
FROM (
SELECT value, classes, year
FROM table
)
PIVOT(sum(value) FOR classes IN (', dec, ‘))’);
This script will first calculate the unique year-month combinations in your table. It will then construct a SQL query using these values and execute it. Please adjust the table and column names according to your actual dataset.