All Products
Search
Document Center

WMSYS.WM_CONCAT/WM_CONCAT

Last Updated: Sep 29, 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);

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)