Reputation: 377
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
Upvotes: 0
Views: 1512
Reputation: 11032
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).
Upvotes: 2