函数名：wmsys.wm_concat

作用：以逗号分隔连接列的值

oracle 10g引入。使用时直接用wm_concat也可以。

以下介绍一些实际使用的状况。介绍实际Case前先建立一个测试table和一些测试数据。


两张表： employee，prject；属性如下

1)employee： 员工ID号，员工名，部门

测试数据

0001    user1    IT
0002    user2    IT

2)prject：项目ID号，负责的员工ID号

测试数据

PN001    0001
PN002    0001
PN003    0001
PN004    0001
PN010    0002
PN011    0002

对应的SQL如下


[sql] view plaincopy

  1. create table employee  

  2. (  

  3.   ID varchar(4) NOT NULL PRIMARY KEY,  

  4.   NAME varchar(20),  

  5.   DEPT varchar(20)  

  6. );  

  7. create table prject  

  8. (  

  9.    PROJECTID varchar(5) NOT NULL PRIMARY KEY,  

  10.    OWNERID varchar(20)  

  11. );  

  12.   

  13. INSERT INTO employee(ID,NAME,DEPT) Values(‘0001’,‘user1’,‘IT’);  

  14. INSERT INTO employee(ID,NAME,DEPT) Values(‘0002’,‘user2’,‘IT’);  

  15.   

  16. INSERT INTO prject(PROJECTID,OWNERID) Values(‘PN001’,‘0001’);  

  17. INSERT INTO prject(PROJECTID,OWNERID) Values(‘PN002’,‘0001’);  

  18. INSERT INTO prject(PROJECTID,OWNERID) Values(‘PN003’,‘0001’);  

  19. INSERT INTO prject(PROJECTID,OWNERID) Values(‘PN004’,‘0001’);  

  20. INSERT INTO prject(PROJECTID,OWNERID) Values(‘PN010’,‘0002’);  

  21. INSERT INTO prject(PROJECTID,OWNERID) Values(‘PN011’,‘0002’);  

 


Case 1: 列转换行。 以一行显示所有员工的名字

     select wmsys.wm_concat(NAME) from employee;

     结果： user1,user2


Case 2: join 两张table , 计算员工负责的 项目个数的例子.


[sql] view plaincopy

  1. select t1.ID,t1.DEPT,t2.pcount from  

  2. (select ID,NAME,DEPT from employee) t1 left outer join   

  3. (select OWNERID,trunc(length(replace(wm_concat(PROJECTID),‘,’,”))/5) as pcount from prject group by OWNERID) t2 on t1.ID = t2.OWNERID;  

结果：

 

0001    IT    4
0002    IT    2

此Case如果使用Count替代的话也可以，而且写法更简单，但是table很复杂的时候使用count不能达成时，可以考虑这个方式， 此处附上count方式


[sql] view plaincopy

  1. select t1.ID,t1.DEPT,t2.pcount from  

  2. (select ID,NAME,DEPT from employee) t1 left outer join   

  3. (select OWNERID,count(PROJECTID) as pcount from prject group by OWNERID) t2 on t1.ID = t2.OWNERID;