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