Description

The group_concat function is used to concatenate a group of string values into a single string. This function is very useful when dealing with multiple rows or records, as it can merge results from the same group into one string, commonly used in report generation and data aggregation scenarios.

Syntax

group_concat(expression [SEPARATOR sep_string])

Parameters

• expression: The column or expression to concatenate.
• sep_string: (Optional) The string used as a separator. If omitted, Lakehouse uses a comma (,) as the default separator.

Return Results

• Returns a string containing all concatenated non-NULL values, separated by the specified delimiter.

Example Usage

SELECT a,group_concat(b SEPARATOR "-")
    FROM VALUES ('A1', 2), ('A1', 1), ('A2', 3), ('A1', 1) tab(a, b) group by a ;
+----+-------------------+
| a  | WM_CONCAT('-', b) |
+----+-------------------+
| A2 | 3                 |
| A1 | 2-1-1             |
+----+-------------------+

Notes

• If the resulting string exceeds the system's maximum length limit, group_concat may throw an error. In Lakehouse, this limit can be adjusted by setting the table property cz.storage.write.max.string.bytes system variable.
• When using the group_concat function, note that NULL values will be ignored and not included in the final string.