Purpose
This function is used to convert columns to rows and concatenate the values of the measure column. It can be used as an aggregate or analytic function.
Note
- When used as an analytic function, you must use the
OVERclause to define the window for computation. This function divides the query result set into groups based on one or more expressions in thequery_partition_clause. - When used as an aggregate function, you do not need to use the
OVERkeyword.- 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 set. Optional. |
| measure_expr | Any expression. Null values in the measure column are ignored. |
| OVER | Use the OVER clause to define the window for computation. 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