Search code examples
google-bigquerybigquery-udf

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

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

enter image description here

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

enter image description here

UPDATE

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

bigquery-public-data.covid19_italy.data_by_province

enter image description here

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 enter image description here

Solution

  • 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

    enter image description here