Big Query (SQL) convert multiple columns to rows / array-CodePudding

I have a data source with multiple similar columns that looks like this, with each question as a new column and the corresponding response:

CodePudding user response：

Consider below solution - it works for any number of the questions/columns in your table w/o any changes in code

select id,
  array(
    select as struct regexp_extract(kv[offset(0)], r'\d ') as Question, 
      kv[offset(1)] as Response
    from unnest(regexp_extract_all(to_json_string(t), r',("[^"] ":"[^"]*")')) kvs, 
    unnest([struct(split(trim(kvs, '"'), '":"') as kv)])
  ) Questionnaire
from `project.dataset.table` t

if applied to sample data in your question - output is