Use array element in looped BigQuery SQL query-CodePudding

I am looking for a way to loop through a bigquery array and use the array value as a column name in a select statement inside the loop. Below is an example of what I have tried

declare dimensions array<string>;
set dimensions = ["ad_group", "campaign", "campaign_type"];

for x in (select * from unnest(dimensions) as value)
do 
 SELECT week, x.value as dimension_name, USE ARRAY VALUE HERE AS COLUMN NAME, sum(spend) as spend FROM `table` group by 1,2,3;
end for;

Each element in the array is a column name in the table I am selecting from and I can't seem to reference the array elements as anything other than the string they are as opposed the columns they represent. Is this possible in BigQuery?

CodePudding user response：

Use below approach

declare dimensions array<string>;
set dimensions = ["ad_group", "campaign", "campaign_type"];

for x in (select * from unnest(dimensions) as value)
do 
  execute immediate 'select  week, "' || x.value || '" as dimension_name, ' || x.value || ', sum(spend) as spend FROM `your_table` group by 1,2,3';
end for;