GROUPCONCAT

Syntax

GROUPCONCAT([DISTINCT] expr)

Purpose

This function concatenates the values of multiple rows of expr in a group into one string. You can use DISTINCT to remove duplicates.

Examples

1. Enable the ClickHouse extension function mode.

   obclient> ALTER SYSTEM SET sql_func_extension_mode = "ClickHouse";
   

2. Create a test table named test_tbl1.

   obclient> CREATE TABLE test_tbl1 (col1 INT PRIMARY KEY, col2 INT, col3 VARCHAR(50));
   

3. Insert data into the test_tbl1 table.

   obclient> INSERT INTO test_tbl1 VALUES
       (1, 1, 'X'),
       (2, 1, 'Y'),
       (3, 1, 'X'),
       (4, 2, 'M'),
       (5, 2, 'N');
   

4. Group by col2 and use GROUPCONCAT to concatenate all distinct values of col3 into one 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