Alternatives of array_agg() or string_agg() on redshift
Redshift has a listagg
function you can use instead:
SELECT _bs, LISTAGG(_wbns, ',') FROM bag GROUP BY _bs;
To get an array type back instead of a varchar, you need to combine the LISTAGG
function with the SPLIT_TO_ARRAY
function like so:
SELECT
some_grouping_key,
SPLIT_TO_ARRAY(LISTAGG(col_to_agg, ','), ',')
FROM some_table
GROUP BY 1
you have to use listagg
for reshift
For each group in a query, the LISTAGG aggregate function orders the rows for that group according to the ORDER BY expression, then concatenates the values into a single string.
LISTAGG is a compute-node only function. The function returns an error if the query doesn't reference a user-defined table or Amazon Redshift system table.
Your query will be as like below
select _bs,
listagg(_wbns,',')
within group (order by _wbns) as val
from bag
group by _bs
order by _bs;
for better understanding Listagg
Use listagg function:
select _bs,
listagg(_wbns,',')
within group (order by _bs) as val
from bag
group by _bs