Use of the Oracle built-in function wmsys. wm_concat

Function Name: wmsys. wm_concat

Purpose: Separate the values of the join columns with commas (,).

Oracle 10 Gb introduction. Wm_concat can be used directly.

The following describes the actual usage conditions. Create a test table and some test data before introducing the actual case.

Two tables: employee and prject. The attributes are as follows:

1) Employee: employee ID, employee name, Department

Test Data

0001 user1 it
0002 user2 it

2) prject: Project ID, employee ID

Test Data

Pn 001 0001
Pn 002 0001
Pn003 0001
Pn 004 0001
Pn010 0002
Pn011 0002

The corresponding SQL is as follows:

create table employee(  ID varchar(4) NOT NULL PRIMARY KEY,  NAME varchar(20),  DEPT varchar(20));create table prject(   PROJECTID varchar(5) NOT NULL PRIMARY KEY,   OWNERID varchar(20));INSERT INTO employee(ID,NAME,DEPT) Values('0001','user1','IT');INSERT INTO employee(ID,NAME,DEPT) Values('0002','user2','IT');INSERT INTO prject(PROJECTID,OWNERID) Values('PN001','0001');INSERT INTO prject(PROJECTID,OWNERID) Values('PN002','0001');INSERT INTO prject(PROJECTID,OWNERID) Values('PN003','0001');INSERT INTO prject(PROJECTID,OWNERID) Values('PN004','0001');INSERT INTO prject(PROJECTID,OWNERID) Values('PN010','0002');INSERT INTO prject(PROJECTID,OWNERID) Values('PN011','0002');

Case 1: Convert columns into rows. Display the names of all employees in one row

Select wmsys. wm_concat (name) from employee;

Result: user1, user2

Case 2: Join two tables to calculate the number of projects the employee is responsible.

select t1.ID,t1.DEPT,t2.pcount from(select ID,NAME,DEPT from employee) t1 left outer join (select OWNERID,trunc(length(replace(wm_concat(PROJECTID),',',''))/5) as pcount from prject group by OWNERID) t2 on t1.ID = t2.OWNERID;

Result:

0001 it 4
0002 it 2

This case can also be replaced by count, and the writing method is simpler. However, when the count cannot be achieved when the table is very complex, this method can be considered. Here, the count method is attached.

select t1.ID,t1.DEPT,t2.pcount from(select ID,NAME,DEPT from employee) t1 left outer join (select OWNERID,count(PROJECTID) as pcount from prject group by OWNERID) t2 on t1.ID = t2.OWNERID;