BigQuery - turn columns to array

Question

I have this table:

I'm looking for this table:

I have searched for array functions (array_agg) and it didn't work as expected.

How can I approach this task? Would I have to loop into this column?

My dataset:

SELECT 1 id, 'protoc1' protoc UNION ALL
SELECT 2 id, 'protoc2' protoc UNION ALL
SELECT 3 id, 'protoc3' protoc 

Answer 1

Use below

select id, format('%t', array_agg(protoc) over()) protoc
from your_table      

if applied to sample data in your question - output is

Answer 2

Not exactly sure why you want that output, but I noticed you had the array repeated for all rows, and looked like you wanted the array represented as a specific string -- so here's my attempt at what you're asking:

with sample as (
  SELECT 1 id, 'protoc1' protoc UNION ALL
  SELECT 2 id, 'protoc2' protoc UNION ALL
  SELECT 3 id, 'protoc3' protoc 
), formatted_string as (
  select "[" || ARRAY_TO_STRING(ARRAY_AGG(protoc),', ') || "]" as protoc
  from sample
)
select sample.id, formatted_string.protoc
from formatted_string
cross join sample