GROUP_CONCAT

Purpose

This function returns a concatenated string of values of expr from all rows in the window or group.

Usage Notes

• With DISTINCT, the function considers duplicate values of expr only once.
• The order_clause as function argument specifies the sorting order of the rows within the window or group.
• The SEPARATOR value can be any string constant. Concatenated string constants are also valid. By default the value for SEPARATOR is a single comma ','.
• The order_clause in the over_clause is also optional.
• It is only possible to specify the order_clause once, either in the function arguments or as part of the over_clause. For more information, refer to Analytic Functions section.
• An overflow occurs if the result string is longer than the maximum length supported by type VARCHAR (2 million characters). If such an overflow occurs, Exasol raises an error.

Examples

Aggregate Function

SELECT 
    department, 
    GROUP_CONCAT(id ORDER BY hire_date SEPARATOR ', ') GROUP_CONCAT_RESULT 
FROM employee_table GROUP BY department;

Result

Analytic Function

SELECT 
    id, department, hire_date, 
    GROUP_CONCAT(id ORDER BY hire_date SEPARATOR ',') OVER (PARTITION BY department rows between 1 preceding and 1 following) GROUP_CONCAT_RESULT
from employee_table ORDER BY department, hire_date;

Result