Use array element in looped BigQuery SQL query

Question

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?

Mikhail Berlyant · Accepted Answer · 2022-01-18 18:58:56Z

2

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;

answered Jan 18, 2022 at 18:58

Mikhail Berlyant

174k10 gold badges172 silver badges250 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use array element in looped BigQuery SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related