Concatenates values in colname with a delimiter that is specified by separator.
Usage notes
MaxCompute V2.0 provides additional functions. If the functions that you use involve new data types, you must enable the MaxCompute V2.0 data type edition. The new data types include TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, and BINARY.
Session level: To use the MaxCompute V2.0 data type edition, you must add
set odps.sql.type.system.odps2=true;before the SQL statement that you want to execute, and commit and execute them together.Project level: The project owner can enable the MaxCompute V2.0 data type edition for the project based on the project requirements. The configuration takes effect after 10 to 15 minutes. Sample command:
setproject odps.sql.type.system.odps2=true;For more information about
setproject, see Project operations. For more information about the precautions that you must take when you enable the MaxCompute V2.0 data type edition at the project level, see Data type editions.
If you use an SQL statement that includes multiple aggregate functions and the project resources are insufficient, memory overflow may occur. We recommend that you optimize the SQL statement or purchase computing resources based on your business requirements.
Syntax
string wm_concat(string <separator>, string <colname>)Parameters
separator: required. The delimiter, which is a constant of the STRING type.
colname: required. A value of the STRING type. If the input value is of the BIGINT, DOUBLE, or DATETIME type, it is implicitly converted into the STRING type before calculation.
If the WM_CONCAT function is used together with the within group (order by <col1>[,<col2>…]) expression and the DISTINCT keyword, you must take note of the function syntax. For more information about the syntax of aggregate functions, see Syntax.
Return value
This function is used with the GROUP BY clause. The ORDER BY clause is not specified.
A value of the STRING type is returned. The return value varies based on the following rules:
If the value of separator is not a constant of the STRING type, an error is returned.
If the value of colname is not of the STRING, BIGINT, DOUBLE, or DATETIME type, an error is returned.
If the value of colname is null, the row that contains this value is not used for calculation.
If table_name in the select wm_concat(',', name) from table_name; statement is an empty set, null is returned.
Sample data
This section provides sample source data and examples for you to understand how to use the functions. Create a table named emp and insert the sample data into the table. Sample statement:
create table if not exists emp
(empno bigint,
ename string,
job string,
mgr bigint,
hiredate datetime,
sal bigint,
comm bigint,
deptno bigint);
tunnel upload emp.txt emp; -- Replace emp.txt with the actual path (path and name) to which you upload the data file.The emp.txt file contains the following sample data:
7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10Examples
Example 1: Concatenate the values in the ename column of all employees. Sample statement:
select wm_concat(',', ename) from emp;The following result is returned:
+------------+ | _c0 | +------------+ | SMITH,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER,JACCKA,WELAN,TEBAGE | +------------+Example 2: Use this function with
GROUP BYto group all employees by department (deptno) and concatenate the names (ename) of employees in each department. Sample statement:select deptno, wm_concat(',', ename) from emp group by deptno order by deptno;The following result is returned:
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | CLARK,KING,MILLER,JACCKA,WELAN,TEBAGE | | 20 | SMITH,JONES,SCOTT,ADAMS,FORD | | 30 | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES | +------------+------------+Example 3: Use this function with
GROUP BYto group all employees by department (deptno) and concatenate the salary values of the employees in each department after duplicates are removed. Sample statement:select deptno, wm_concat(distinct ',', sal) from emp group by deptno order by deptno;The following result is returned:
+------------+------------+ | deptno | _c1 | +------------+------------+ | 10 | 1300,2450,5000 | | 20 | 1100,2975,3000,800 | | 30 | 1250,1500,1600,2850,950 | +------------+------------+Example 4: Use this function with
GROUP BYandORDER BYto group all employees by department (deptno), concatenate the salary values (sal) of all employees in each department, and sort the salary values (sal) in a specified order. Sample statement:select deptno, wm_concat(',',sal) within group (order by sal) from emp group by deptno order by deptno;The following result is returned:
+------------+------------+ |deptno|_c1| +------------+------------+ |10|1300,1300,2450,2450,5000,5000| |20|800,1100,2975,3000,3000| |30|950,1250,1250,1500,1600,2850| +------------+------------+Example 5: Use this function with
GROUP BYandORDER BYto sort the specified columns in a specified order. Sample statement:SELECT g, wm_concat ('#', t) WITHIN GROUP (ORDER BY h DESC) AS t_t FROM VALUES ("24",1,10), ("234",2,100), ("555",1,20), ("123", 2,30), ("7",1,11), ("13",3,50) AS tmp (t,g,h) GROUP BY g;The following result is returned:
+------+-----+ | g | t_t | +------+-----+ | 1 | 555#7#24 | | 2 | 234#123 | | 3 | 13 | +------+-----+
Related functions
WM_CONCAT is an aggregate function. For more information about the functions that are used to calculate the average value of multiple input records and to aggregate parameters, see Aggregate functions.