Adding a column to an SQL table and exploding the rows with a set of fixed values for that column

I would like to add a column to an SQL table with unknown columns and explode the entries in that table by a set of fixed values for that column. E.g. Turn

unknown col 1	…	unknown col x
1	…	foo
2	…	bar

into

unknown col 1	…	unknown col x	new col
1	…	foo	1
2	…	bar	1
1	…	foo	2
2	…	bar	2

The number of unknown columns is also unknown. I know the query to turn the original table into

unknown col 1	…	unknown col x	new col
1	…	foo	1
2	…	bar	1

I don’t know the INSERT query that would turn it in to the desired table further above. The table is on Google BigQuery.

p.s: I can think of workarounds, e.g. multiply the number of rows in the original table by n, where n is the number of values the new column can take, then add the column and set the value based on the row number (which is not trivial to set) for each row. I am looking for a cleaner way.

Answer

add a column to an SQL table with unknown columns and explode the entries in that table by a set of fixed values for that column.

Below should do the “trick” – example

with new_col_values as (
  select [1, 2, 3, 4] values
)
select t.*, val
from `project.dataset.your_table` t, 
new_col_values, unnest(values) val