google-bigqueryprocedurecreate-tableexecute-immediate

GBQ Execute Immediate Save Results to Table

Hi All I am trying to same the results from this query into a table on GBQ. I have linked the previous question for reference

GBQ Convert Data types en Mass

Starting Code

execute immediate (select '''
select `Group`, ''' || (select string_agg('cast(' || Fruit || ' as Numeric) as ' || Fruit ) from (
select regexp_extract_all(to_json_string((select as struct * except(`Group`) from unnest([t]))), r'"([^"]+)":') Fruits
from `project.dataset.table` t limit 1), unnest(Fruits) Fruit)  ||   
''' from `project.dataset.table`''');

Various Attempts

Create or Replace Table1 as
execute immediate (select '''
select `Group`, ''' || (select string_agg('cast(' || Fruit || ' as Numeric) as ' || Fruit ) from (
select regexp_extract_all(to_json_string((select as struct * except(`Group`) from unnest([t]))), r'"([^"]+)":') Fruits
from `project.dataset.table` t limit 1), unnest(Fruits) Fruit)  ||   
''' from `project.dataset.table`''');

execute immediate (select '''
select `Group`, ''' || (select string_agg('cast(' || Fruit || ' as Numeric) as ' || Fruit ) 
INTO Table1
from (
select regexp_extract_all(to_json_string((select as struct * except(`Group`) from unnest([t]))), r'"([^"]+)":') Fruits
from `project.dataset.table` t limit 1), unnest(Fruits) Fruit)  ||   
''' from `project.dataset.table`''');

execute immediate (select '''
select `Group`, ''' || (select string_agg('cast(' || Fruit || ' as Numeric) as ' || Fruit ) from (
select regexp_extract_all(to_json_string((select as struct * except(`Group`) from unnest([t]))), r'"([^"]+)":') Fruits
from `project.dataset.table` t limit 1), unnest(Fruits) Fruit)  ||   
''' from `project.dataset.table`''') INTO Table1; 
 

execute immediate (select '''
select `Group`, ''' || (select string_agg('cast(' || Fruit || ' as Numeric) as ' || Fruit ) from (
select regexp_extract_all(to_json_string((select as struct * except(`Group`) from unnest([t]))), r'"([^"]+)":') Fruits
from `project.dataset.table` t limit 1), unnest(Fruits) Fruit)  ||   
''' from `project.dataset.table`''' INTO Table1);
Solution

  • Use below approach

    execute immediate (select '''create or replace table `project.dataset.table2` as 
select `Group`, ''' || (select string_agg('cast(' || Fruit || ' as float64) as ' || Fruit ) from (
select regexp_extract_all(to_json_string((select as struct * except(`Group`) from unnest([t]))), r'"([^"]+)":') Fruits
from `project.dataset.table` t limit 1), unnest(Fruits) Fruit)  ||   
''' from `project.dataset.table`''');