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. It considers all occurrences with ALL. If the function contains neither ALL nor DISTINCT, the default is ALL.
- The order_clause as function argument specifies the sorting order of the rows within the window or group.
- 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.
- 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 |