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
Use below
select id, format('%t', array_agg(protoc) over()) protoc
from your_table
if applied to sample data in your question - output is
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
