how do you pivot rows into a single comma separated string?

Redshift doesn’t support string_agg() function but has the listagg() function which I believe is equivalent. See: https://docs.aws.amazon.com/redshift/latest/dg/r_LISTAGG.html

Listagg() support DISTINCT and even has a window function form. Does this not produce your desired results?

select b.member_id, listagg(distinct c.name, ',') 
from bookings b 
join category c on c.member_id = b.member_id 
group by 1;

As for the error message in the update, that is Redshift’s cryptic way to say that you have attempted to perform a leader node only operation on a compute node (or something of that ilk). I don’t see why you would get that unless string_agg() is supported as a leader only operation (generate_series() is an example of a function only supported on the leader node).

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top