GROUP_CONCAT

Purpose

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

Syntax

group_concat::=

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 default 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
DEPARTMENT GROUP_CONCAT_RESULT
ACCOUNTS 2005, 2003, 2002, 2004, 2001
HR 1003, 1002, 1004, 1001

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
ID DEPARTMENT HIRE_DATE GROUP_CONCAT_RESULT
2005 ACCOUNTS 2013-01-01 2005,2003
2003 ACCOUNTS 2015-07-01 2005,2003,2002
2002 ACCOUNTS 2017-01-01 2003,2002,2004
2004 ACCOUNTS 2017-01-01 2002,2004,2001
2001 ACCOUNTS 2018-07-01 2004,2001
1003 HR 2014-01-01 1003,1002
1002 HR 2016-01-01 1003,1002,1004
1004 HR 2016-01-01 1002,1004,1001
1001 HR 2018-01-01 1004,1001