All Products
Search
Document Center

WMSYS.WM_CONCAT/WM_CONCAT

Last Updated: Jun 18, 2021

You can use the WM_CONCAT function to aggregate data from a number of measure columns into a single row. As a single-set aggregate function, the WM_CONCAT function operates on all rows and returns a single output row. As a group-set aggregate function, the WM_CONCAT function operates on each group specified in the GROUP BY clause and returns an output row. As an analytic function, the WM_CONCAT function divides the query result set into groups based on one or more expressions in the query_partition_clause.

Syntax

WM_CONCAT ([DISTINCT] measure_expr) [OVER query_partition_clause]

To use it as an analytic function, you need to use the complete syntax of the window function to operate on a set of rows and return multiple values. To use it as an aggregate function, it operates on a set of rows and returns a single value. You do not need to add the OVER keyword.

Parameters

Parameter

Description

OVER

You can use the OVER clause to define a window over the data on which the function operates.

measure_expr

An expression of any type. Null values in the measure column are ignored.

DISTINCT

If you specify the DISTINCT operator, duplicates are eliminated from the result set.

Return type

This function returns data of the CLOB data type.

Examples

Using WMSYS.WM_CONCAT/WM_CONCAT as an analytic function

Execute the following statement to create Table employees and insert data into it:

CREATE TABLE employees (department_id INT,manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT); INSERT INTO employees VALUES(30, 100, 'Raphaely', '2017-07-01', 1700); INSERT INTO employees VALUES(30, 100, 'De Haan', '2018-05-01',11000); INSERT INTO employees VALUES(40, 100, 'Errazuriz', '2017-07-21', 1400); INSERT INTO employees VALUES(50, 100, 'Hartstein', '2019-05-01',14000); INSERT INTO employees VALUES(50, 100, 'Raphaely', '2017-07-22', 1700); INSERT INTO employees VALUES(70, 100, 'Weiss', '2019-07-11',13500); INSERT INTO employees VALUES(90, 100, 'Russell', '2019-10-05', 13000); INSERT INTO employees VALUES(90,100, 'Partners', '2018-12-01',14000);
INSERT INTO employees VALUES(30, 100, 'Raphaely', '2017-07-01', 1700); INSERT INTO employees VALUES(30, 100, 'De Haan', '2018-05-01',11000); INSERT INTO employees VALUES(40, 100, 'Errazuriz', '2017-07-21', 1400); INSERT INTO employees VALUES(50, 100, 'Hartstein', '2019-05-01',14000); INSERT INTO employees VALUES(50, 100, 'Raphaely', '2017-07-22', 1700); INSERT INTO employees VALUES(70, 100, 'Weiss', '2019-07-11',13500); INSERT INTO employees VALUES(90, 100, 'Russell', '2019-10-05', 13000); INSERT INTO employees VALUES(90,100, 'Partners', '2018-12-01',14000);

查询 2019 年 10 月 10 日之前雇用的雇员,以及该雇员的部门,雇用日期以及该部门中的其他雇员。 执行以下语句:

SELECT department_id "Dept", hiredate "Date", last_name "Name",wmsys.wm_concat(last_name) 
 OVER (PARTITION BY department_id) as "Emp_list"
FROM employees WHERE hiredate < '2019-10-10' ORDER BY "Dept", "Date", "Name";

查询结果如下:

+------+------------+-----------+--------------------+
| Dept | Date       | 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 |
|   50 | 2019-05-01 | Hartstein | Hartstein,Raphaely |
|   70 | 2019-07-11 | Weiss     | Weiss              |
|   90 | 2018-12-01 | Partners  | Russell,Partners   |
|   90 | 2019-10-05 | Russell   | Russell,Partners   |
+------+------------+-----------+--------------------+
8 rows in set (0.01 sec)

聚合函数示例

建表 employees,并向里面插入数据,执行以下语句:

CREATE TABLE employees (department_id INT,manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(30, 100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(30, 100, 'De Haan', '2018-05-01',11000);      
INSERT INTO employees VALUES(30, 100, 'Errazuriz', '2017-07-01', 1400);
INSERT INTO employees VALUES(30, 100, 'Hartstein', '2019-05-01',14000);     
INSERT INTO employees VALUES(30, 100, 'Raphaely', '2017-07-01', 1700);
INSERT INTO employees VALUES(30, 100, 'Weiss',  '2019-07-01',13500);     
INSERT INTO employees VALUES(30, 100, 'Russell', '2019-07-01', 13000);
INSERT INTO employees VALUES(30,100, 'Partners',  '2018-12-01',14000);

查询第 30 部门的所有员工,并查找最早的雇用日期。 执行以下语句:

SELECT wmsys.wm_concat(last_name) as "Emp_list", MIN(hiredate) as "Earliest"
 FROM employees WHERE department_id = 30;

查询结果如下:

+----------------------------------------------------------------------+------------+
| Emp_list                                                             | Earliest   |
+----------------------------------------------------------------------+------------+
| Raphaely,De Haan,Errazuriz,Hartstein,Raphaely,Weiss,Russell,Partners | 2017-07-01 |
+----------------------------------------------------------------------+------------+
1 row in set (0.01 sec)