Declaration
GROUPCONCAT([DISTINCT] expr)
Description
This function concatenates the values of multiple expr rows within a group into a single string. You can use DISTINCT to remove duplicates.
Notice
This is a ClickHouse-compatible function. It is disabled by default and must be enabled by setting the sql_func_extension_mode parameter. For more information, see sql_func_extension_mode.
Examples
Enable the ClickHouse extension function mode.
obclient> ALTER SYSTEM SET sql_func_extension_mode = "ClickHouse";Create the test table
test_tbl1.obclient> CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 INT, col3 VARCHAR(50));Insert data into the
test_tbl1table.obclient> INSERT INTO test_tbl1 VALUES (1, 1, 'X'), (2, 1, 'Y'), (3, 1, 'X'), (4, 2, 'M'), (5, 2, 'N');Group by
col2and useGROUPCONCATto concatenate all distinctcol3values into a single string.obclient> SELECT col2, GROUPCONCAT(DISTINCT col3) FROM test_tbl1 GROUP BY col2;The return result is as follows:
+------+----------------------------+ | col2 | GROUPCONCAT(DISTINCT col3) | +------+----------------------------+ | 2 | MN | | 1 | XY | +------+----------------------------+ 2 rows in set