CODEWITHSUNDEEP
sqlamazon-redshiftdbeaver
Sarah Rahman
Sarah Rahman

Reputation: 203

How to Concatenate 2 Columns using SQL in DBeaver connected to Redshift

Here's an example table:

 account_no     season      animal 
      A         spring      sheep
      A         spring      cow
      A         winter      pig
      B         winter      lamb

I'm using DBeaver connected to Redshift and I'd like to GROUP BY account_no and season and concatenate the animals into a single column as shown below:

account_no      season     animalList 
     A          spring     sheep, cow
     A          winter     pig   
     B          winter     lamb

I've tried the STRING_AGG function such as

SELECT STRING_AGG(animal,',') AS animalList
FROM Animals
GROUP BY account_no, season

However, I'm getting the error below:

Error occurred during SQL query execution

Reason: SQL Error [500310] [42883]: [Amazon](500310) Invalid operation: function string_agg(character varying, "unknown") does not exist;

I've tried adding varchar as suggested by other threads but I'm still getting the same error

 SELECT STRING_AGG(animal: : varchar,',') AS animalList
    FROM Animals
    GROUP BY account_no, season

Appreciate your help!

Upvotes: 1

Views: 4026

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Redshift calls the function listagg():

SELECT LISTAGG(animal, ',') WITHIN GROUP (ORDER BY animal) AS animalList
FROM Animals
GROUP BY account_no, season;

This is not unreasonable, because this is the standard name for the function.

Upvotes: 1

Related Questions