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);
Execute the following statement to query employees hired before October 10, 2019, their department, the hire date, and other employees in their department:
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";
Result:
+------+------------+-----------+--------------------+
| 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)
Using WMSYS.WM_CONCAT/WM_CONCAT as an aggregate 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(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);
Execute the following statement to query all employees in the 30th Department and find the earliest hire date:
SELECT wmsys.wm_concat(last_name) as "Emp_list", MIN(hiredate) as "Earliest"
FROM employees WHERE department_id = 30;
Result:
+----------------------------------------------------------------------+------------+
| Emp_list | Earliest |
+----------------------------------------------------------------------+------------+
| Raphaely,De Haan,Errazuriz,Hartstein,Raphaely,Weiss,Russell,Partners | 2017-07-01 |
+----------------------------------------------------------------------+------------+
1 row in set (0.01 sec)