How to pivot rows into a single comma separated string

Important update:

When I try to use the suggested string_agg method I get this error – Specified types or functions (one per INFO message) not supported on Redshift tables.

Original question

I have a query but I’m struggling to “pivot” multiple rows into a single column of strings.

I have a member and a category table and each member can have multiple categories (this is a simplification of the scenario).

So I need to write a query to display what categories each member has, so each member has multiple categories. When I was working in the Microsoft world I was able to use pivot but now in Postgres I’m not able to find an equivalent method.

I’ve seen references to crosstab and a few other methods but when trying I get errors saying the function isn’t recognised.

My attempt!

select                              
    m.member_id,                                
    array.join(c.category, ",") -- this is more like a programming approach but I need something similar to this
from member m
from join category c ON c.member_id = m.id
group by 1      

Example with dataset

https://dbfiddle.uk/?rdbms=postgres_13&fiddle=8ea4998f75f7db83d2360ff01bf02c82

I’m using Navicat Premium as my “editor”

A second attempt

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

Answer

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).