Menu
How to GROUP BY and CONCATENATE fields in Redshift e.g. If I have table
ID COMPANY_ID EMPLOYEE
1 1 Anna
2 1 Bill
3 2 Carol
4 2 Dave
How can I get result like this
COMPANY_ID EMPLOYEE
1 Anna, Bill
2 Carol, Dave
There are some solutions for PostgreSQL, but none of functions mentioned in those answers are available in Redshift rightnow.
858
To concatenate more than two expressions, use nested CONCAT functions. The concatenation operator ( || ) between two expressions produces the same results as the CONCAT function. For both the CONCAT function and the concatenation operator, if one or both expressions is null, the result of the concatenation is null.
For each group in a query, the LISTAGG window function orders the rows for that group according to the ORDER BY expression, then concatenates the values into a single string.
Using the + operator is the most common way to concatenate two strings in Java. You can provide either a variable, a number, or a String literal (which is always surrounded by double quotes). Be sure to add a space so that when the combined string is printed, its words are separated properly.
If you have SQL Server 2017 or later, using CONCAT_WS() is the best way to concatenate multiple columns to a string value. Here you have to specify the separator in the form of char, nchar, varchar or nchar as the first argument. Then you can pass on the columns to concatenate in the subsequent arguments.
Well, I am a little late but the announcement about this feature happened on 3rd Aug 2015. Redshift has introduced LISTAGG window function that makes it possible to do so now. Here is a quick solution to your problem - may or may not be useful but putting it here so that people will know!
SELECT COMPANY_ID,
LISTAGG(EMPLOYEE,', ')
WITHIN GROUP (ORDER BY EMPLOYEE)
OVER (PARTITION BY COMPANY_ID) AS EMPLOYEE
FROM YOUR_TABLE
ORDER BY COMPANY_ID
I was happy to see this feature, and many of our production scripts are up for upgrade with all the new features Redshift keeps adding.
Here is the documentation about the function
179
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
