title: GBQ Execute Immediate into a CTE
body: <p>I am building an application/script for users that do not have write access to the database. Normally I would use Execute Immediate and save that result into a table, and then pull from that table and continue on with the script. Is there a way to save results from Execute Immediate in either a sub query or CTE so that the script can continue on?</p>
  <p>Example code where results are put into a table</p>
  <pre><code>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'&quot;([^&quot;]+)&quot;:') Fruits
  from `project.dataset.table` t limit 1), unnest(Fruits) Fruit)  ||   
  ''' from `project.dataset.table`''')
  ;
  
  </code></pre>
  <p>I would need something more along the lines of this, but it doesn't work</p>
  <pre><code>WITH CTEtable2 as (
  execute immediate (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'&quot;([^&quot;]+)&quot;:') Fruits
  from `project.dataset.table` t limit 1), unnest(Fruits) Fruit)  ||   
  ''' from `project.dataset.table`''')
  )
  
  SELECT *
  FROM CTEtable2 
  </code></pre>