BigQuery scripting: running a SQL query based on each row included in a table column

Question

My origin table includes url_id and SQL [standard sql, string]

I would like to run each of the SQL statements in the string column "SQL" and join the outcome with the correspondent url_id

expected outcome

UPDATE

to make this easy to reproduce I′ve imported a similar schema from public BQ:

bigquery-public-data.covid19_italy.data_by_province

if I run

select string_agg(
    format('select %i as url_id, (%s) as simpleSKU', url_id, sql), 
    ' union all '   )

FROM `xxx.staging.test_query

it returns

question from:https://stackoverflow.com/questions/65877988/bigquery-scripting-running-a-sql-query-based-on-each-row-included-in-a-table-co

Answer 1

Consider below

execute immediate (
  select 'select url_id, region_name from (' || string_agg(
    format('select %i as url_id, array(%s) as region_names', url_id, sql), 
    ' union all '
  ) || '), unnest(region_names) as region_name'
  from your_table
);   

if applied to sample data in your question - output it