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

Question

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

and I would like to convert it to use an array with two paired columns so that it ends up looking like this instead, with just two columns, Question and Response, and each of the legacy columns just having it's own key (1,2,3 etc): Desired

Please bear with me, I'm sure this is very simple and I think needs to use array_agg or even possibly an unpivot but I've trawled past posts and can't find any with a similar solution for the value of the Question column relating to the name of multiple columns from the "flat" source / assigning the value in the new field based on the originating column name.

I have this, but I need to get the Question/Response pairings....

select ID, array_agg(response ignore nulls) Questionnaire
from datasourcename,
unnest([Q1Response, Q2Response, ]) response
group by ID

Any support much appreciated (first post!)

Answer 1

Try this:

with mytable as (
  select 1 as id, 'a' as q1response, 'c' as q2response, 'a' as q3response, 'd' as q4response union all
  select 2, 'b', 'a', 'a', 'd' union all
  select 3, 'a', 'b', 'b', 'a'
)
select
  id,
  [ struct('1' as question, q1response as response),
    struct('2' as question, q2response as response),
    struct('3' as question, q3response as response),
    struct('4' as question, q4response as response)
  ] as q
from mytable