Skip to content
Advertisement

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.

Advertisement

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

enter image description here

5 People found this is helpful
Advertisement