Purpose
This function is used to convert columns into rows and concatenate the values of the metric columns. It can be used as an aggregate or analytic function.
Note
- When used as an analytic function, the
OVERclause must be used to define the window for calculation. This function partitions the query result set into groups based on one or more expressions in thequery_partition_clause. - When used as an aggregate function, the
OVERkeyword is not required.- As a single-set aggregate function,
WM_CONCAToperates on all rows and returns a single output row. - As a group aggregate,
WM_CONCAToperates on each group defined by theGROUP BYclause and returns output rows.
- As a single-set aggregate function,
Syntax
WM_CONCAT ([DISTINCT] measure_expr) [OVER query_partition_clause]
Parameters
Parameter |
Description |
|---|---|
| DISTINCT | If specified, duplicate values are removed from the result. Optional. |
| measure_expr | Any expression. Null values in the metric column are ignored. |
| OVER | Use the OVER clause to define the window for calculation. For more information, see Analytic Function Description. |
Return type
Returns a value of the CLOB type.
Examples
Assume that the employees table has been created.
obclient> SELECT * FROM employees;
+---------------+-----------+------------+--------+
| DEPARTMENT_ID | LAST_NAME | HIREDATE | SALARY |
+---------------+-----------+------------+--------+
| 30 | Raphaely | 2017-07-01 | 1700 |
| 30 | De Haan | 2018-05-01 | 11000 |
| 40 | Errazuriz | 2017-07-21 | 1400 |
| 50 | Hartstein | 2019-10-05 | 14000 |
| 50 | Raphaely | 2017-07-22 | 1700 |
| 50 | Weiss | 2019-10-05 | 13500 |
| 90 | Russell | 2019-07-11 | 13000 |
| 90 | Partners | 2018-12-01 | 14000 |
+---------------+-----------+------------+--------+
8 rows in set
Aggregate function example
Concatenate all names in the last_name column into a single row.
obclient> SELECT WM_CONCAT(last_name) "Emp_list" FROM employees;
+----------------------------------------------------------------------+
| Emp_list |
+----------------------------------------------------------------------+
| Raphaely,De Haan,Errazuriz,Hartstein,Raphaely,Weiss,Russell,Partners |
+----------------------------------------------------------------------+
1 row in set
Analytic function example
Group by the department_id column and query all last_name values in the same group.
obclient> SELECT department_id,hiredate,last_name,
WM_CONCAT(last_name) OVER (PARTITION BY department_id) as "Emp_list"
FROM employees
ORDER BY 1,2;
+---------------+------------+-----------+--------------------------+
| DEPARTMENT_ID | HIREDATE | LAST_NAME | Emp_list |
+---------------+------------+-----------+--------------------------+
| 30 | 2017-07-01 | Raphaely | Raphaely,De Haan |
| 30 | 2018-05-01 | De Haan | Raphaely,De Haan |
| 40 | 2017-07-21 | Errazuriz | Errazuriz |
| 50 | 2017-07-22 | Raphaely | Hartstein,Raphaely,Weiss |
| 50 | 2019-10-05 | Hartstein | Hartstein,Raphaely,Weiss |
| 50 | 2019-10-05 | Weiss | Hartstein,Raphaely,Weiss |
| 90 | 2018-12-01 | Partners | Russell,Partners |
| 90 | 2019-07-11 | Russell | Russell,Partners |
+---------------+------------+-----------+--------------------------+
8 rows in set