Scalable method to union all column in a table – Big query

Let’s say I have

col1	col2
a	c
b	d

and I want to have only one column, I’d do

SELECT col1 FROM TABLE 
UNION ALL 
SELECT col2 FROM TABLE

and get

col1
a
b
c
d

But now let’s say I have 100 columns (all strings, same data type), what’s the best way to add all the columns together without doing a union all for all the columns? I found solutions on SQL server but not working in Big Query.

Answer

Consider below approach

select value
from your_table t,
unnest(bqutil.fn.json_extract_values(to_json_string(t))) value            

if applied to sample data in your question – output is