GROUP BY using parameters in Ads Data Hub (BigQuery)

Question

I am working on Google ads data hub and am relatively new to bigquery (sql in general). In a certain case, I am trying to do a dynamic group by based on a parameter input (I believe the parameter needs to be an array?)

So if say my table has four fields (two are numerical and two are string)

And I want a scenario where I am doing a group by on either one of the string variable or both of them together

So based on my paramter input, my data will be grouped by on the selected input and a certain mathematical operation would be applied to the numerical data (say average)

Currently, I can just make combinations of different string columns (lets say I have age and name as my columns so I have one age column, one name column and one age_name column) but this workaround falls apart when my string columns go beyond a number since there will be way too many combinations possible.

Is there a way to dynamically provide what columns I want to group by my table on and select those columns and numerical values (post aggregation) in my output?

Sample Input :

Pokemon	Location	Value
Jolteon	City1	230
Jolteon	City2	210
Umbreon	City2	240
Umbreon	City2	180
Umbreon	City3	180
Espeon	City3	260
Espeon	City3	100
Espeon	City4	300

If I supply @hierarchy paramter as a string array. The aggregation logic is average here.

Output if input is "pokemon"

Pokemon	Value
Jolteon	220
Umbreon	200
Espeon	220

Output if input is "pokemon,location"

Pokemon	Location	Value
Jolteon	City1	230
Jolteon	City2	210
Umbreon	City2	210
Umbreon	City3	180
Espeon	City3	180
Espeon	City4	300

Note: Please note that this is just in the case of 2 fields but if my potential paramater input can have more than 3,4 factors and final output have data grouped by those 3,4 factors and then the pre decided aggregation logic on the numerical fields.

Answer 1

If I supply @hierarchy parameter as a string array ...

Consider below

DECLARE hierarchy ARRAY<STRING>;
SET hierarchy = ['pokemon', 'location'];
EXECUTE IMMEDIATE 'SELECT ' || ARRAY_TO_STRING(hierarchy, ',') || 
', AVG(value) as value FROM `project.dataset.table` GROUP BY ' || ARRAY_TO_STRING(hierarchy, ',');  

If applied to sample data in your question - output is

In case of SET hierarchy = ['pokemon']; - output is

Answer 2

Try EXECUTE_IMMEDIATE:

DECLARE columns STRING;

CREATE TEMPORARY TABLE mytable as
select "Jolteon" as pokemon, "City1" as location, 230 as value union all
select "Jolteon", "City2", 210 union all
select "Umbreon", "City2", 240 union all
select "Umbreon", "City2", 180 union all
select "Umbreon", "City3", 180 union all
select "Espeon", "City3", 260 union all
select "Espeon", "City3", 100 union all
select "Espeon", "City4", 300
;

SET columns="pokemon, location";
EXECUTE IMMEDIATE "SELECT " || columns || ", AVG(value) as value FROM mytable GROUP BY " || columns;