1

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?

Example code where results are put into a table

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`''')
;

I would need something more along the lines of this, but it doesn't work

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'"([^"]+)":') Fruits
from `project.dataset.table` t limit 1), unnest(Fruits) Fruit)  ||   
''' from `project.dataset.table`''')
)

SELECT *
FROM CTEtable2
UserX
  • 157
  • 13
  • 3
    Q: `Is there a way to save results from Execute Immediate in either a sub query or CTE so that the script can continue on?` - A: `Unfortunately, No!` – Mikhail Berlyant May 12 '22 at 22:02
  • also check out [What is the XY problem?](https://meta.stackexchange.com/a/66378/507852) - I think you got trapped into it here – Mikhail Berlyant May 12 '22 at 22:07
  • @MikhailBerlyant will repost a different question with the whole problem. Essentially trying to transpose/pivot values in a column to be the columns of the table and make the table horizontal instead of vertical. I the amount do values can change, so I believe the solution needs to use dynamic sql – UserX May 12 '22 at 22:21
  • To be honest - I don't see how that new post/question is different from this one! Just wanted to mention – Mikhail Berlyant May 12 '22 at 23:34
  • 1
    @MikhailBerlyant This was asking if there is a way to do Execute Immediate into a CTE..... There is not..... The new question is asking if there is another method to transpose I am unaware of other than using pivot and execute immediate for dynamic columns. One has to do with data storage, while the other is data manipulations. Two difference areas, atleast in my eyes – UserX May 12 '22 at 23:44
  • as a suggestions - search here on SO, I have multiple related posts before execute immediate was available. you might find some useful options to explore – Mikhail Berlyant May 12 '22 at 23:54
  • @MikhailBerlyant did look through posts, such as https://stackoverflow.com/questions/61528721/string-aggregtion-to-create-pivot-columns-in-big-query/61530181#61530181,. However it still uses Execute Immediate – UserX May 14 '22 at 01:30

0 Answers0