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.

[ms4446]

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.

[alexsp]

Hi. Thank you for your reply.

So, it did not work. I got the following message:

Invalid value: Invalid field name ",dec,". Fields must contain the allowed characters, and be at most 300 characters long.  

But I have only 135 characters.

And just a little correction, is (""" code... ''''') instead ('code...'). At least for me worked this way. 

[ms4446]

Sorry for the confusion. The issue is not with the length of the variable, but rather how it's being concatenated into the SQL statement.

In BigQuery's EXECUTE IMMEDIATE clause, you can't directly concatenate variables into a SQL string using the CONCAT function or || operator. Instead, you should use FORMAT function to interpolate the variables into the SQL string.

Here's the corrected code:

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 FORMAT("""
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 (%s))
""", dec);

In this code, the FORMAT function is used to replace the %s placeholder in the SQL string with the value of dec. This will correctly insert the pivot values into the PIVOT clause.

[alexsp]

I used your suggestion, but now I got a new different error:

"PIVOT(sum(values) FOR classes IN (%s)) '2023-6','2023-5','2023-4','2023-3','2023-1','2022-12','2022-11','2022-10','2023-2','2022-9','2023-7','2022-8','2022-5','2022-6','2022-7'`, Syntax error: Unexpected "%" at [25:19]"

[ms4446]

The issue is caused by how the FORMAT function works. FORMAT doesn't actually replace %s with the value of dec inside the EXECUTE IMMEDIATE statement. Instead, it just puts the value of dec at the end of the SQL string.

The correct way to do this is to construct the entire SQL string first, including the pivot values, and then execute it. Try this corrected code:

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`);

DECLARE sql STRING;
SET sql = 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, '))');

EXECUTE IMMEDIATE sql;