Note
This function is available starting with V5.0.1.
Declaration
STRING_AGG([DISTINCT] expr, separator
[ORDER BY {unsigned_integer | col_name | expr}
[ASC | DESC] [,col_name ...]])
Description
STRING_AGG is a string aggregation function that concatenates the string values within a group into a single string using a specified delimiter, similar to GROUP_CONCAT.
Parameters
Parameter |
Description |
|---|---|
| expr | The string expression to be connected. |
| separator | The delimiter used to concatenate the strings. |
| DISTINCT | Optional. Yes.exprDeduplicate the data and then reconnect. |
| ORDER BY | Optional. Specifies the sorting method for each value before connection. |
Response parameters
- Returns the concatenated string.
- Rows where
exprisNULLwill be skipped. - Returns
NULLif allexprvalues within the group areNULL.
Examples
obclient> CREATE TABLE t_string_agg (id INT, val VARCHAR(50));
Query OK, 0 rows affected
obclient> INSERT INTO t_string_agg VALUES (1, 'apple'), (1, 'banana'), (1, 'cherry'), (2, 'dog'), (2, 'cat');
Query OK, 5 rows affected
obclient> SELECT id, STRING_AGG(val, ',') AS agg_val FROM t_string_agg GROUP BY id;
The return result is as follows:
+----+---------------+
| id | agg_val |
+----+---------------+
| 1 | apple,banana,cherry |
| 2 | dog,cat |
+----+---------------+
2 rows in set