WMSYS.WM_CONCAT 函數的用法

WMSYS.WM_CONCAT 函數的用法 

 分类： 数据库及语言

select t.rank, t.Name from t_menu_item t; 

 10 CLARK 

 10 KING 

 10 MILLER 

 20 ADAMS 

 20 FORD 

 20 JONES 

 20 SCOTT

 20 SMITH 

 30 ALLEN 

 30 BLAKE 

 30 JAMES 

 30 MARTIN 

 30 TURNER 

 30 WARD

我们通过 10g 所提供的 WMSYS.WM_CONCAT 函数即可以完成 行转列的效果

 select t.rank, WMSYS.WM_CONCAT(t.Name) TIME From t_menu_item t GROUP BY t.rank;

DEPTNO ENAME------ ---------- 

 10 CLARK, KING, MILLER 

 20 ADAMS, FORD, JONES, SCOTT, SMITH 

 30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

例子如下：

SQL> create table idtable (id number,name varchar2(30));

Table createdSQL> insert into idtable values(10,'ab');

1 row insertedSQL> insert into idtable values(10,'bc');

1 row insertedSQL> insert into idtable values(10,'cd');

1 row insertedSQL> insert into idtable values(20,'hi');

1 row insertedSQL> insert into idtable values(20,'ij');

1 row insertedSQL> insert into idtable values(20,'mn');

1 row insertedSQL> select * from idtable;

 ID NAME

 10 ab 

 10 bc 

 10 cd 

 20 hi 

 20 ij 

 20 mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) name from idtable2 group by id; 

 ID NAME

10 ab,bc,cd 

 20 hi,ij,mn

SQL> select id,wmsys.wm_concat(name) over (order by id) name from idtable; 

 ID NAME

10 ab,bc,cd 

 10 ab,bc,cd 

 10 ab,bc,cd 

 20 ab,bc,cd,hi,ij,mn 

 20 ab,bc,cd,hi,ij,mn 

 20 ab,bc,cd,hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (order by id,name) name from idtable; 

 ID NAME

 10 ab 

 10 ab,bc 

 10 ab,bc,cd

 20 ab,bc,cd,hi 

 20 ab,bc,cd,hi,ij 

 20 ab,bc,cd,hi,ij,mn

6 rows selected个人觉得这个用法比较有趣.

SQL> select id,wmsys.wm_concat(name) over (partition by id) name from idtable; 

 ID NAME

10 ab,bc,cd 

 10 ab,bc,cd 

 10 ab,bc,cd 

 20 hi,ij,mn 

 20 hi,ij,mn 

 20 hi,ij,mn

6 rows selected

SQL> select id,wmsys.wm_concat(name) over (partition by id,name) name from idtable; 

 ID NAME

10 ab 

 10 bc 

 10 cd 

 20 hi 

 20 ij 

 20 mn

6 rows selected 

ps:

wmsys.wm_concat、

sys_connect_by_path、

自定义行数实现行列转换：

CREATE TABLE tab_name(

ID INTEGER NOT NULL PRIMARY KEY,

cName VARCHAR2(20)

);

CREATE TABLE tab_name2(

ID INTEGER NOT NULL,

pName VARCHAR2(20)

);

INSERT INTO tab_name(ID,cName) VALUES (1,'百度');

INSERT INTO tab_name(ID,cName) VALUES (2,'Google');

INSERT INTO tab_name(ID,cName) VALUES (3,'网易');

INSERT INTO tab_name2(ID,pName) VALUES (1,'研发部');

INSERT INTO tab_name2(ID,pName) VALUES (1,'市场部');

INSERT INTO tab_name2(ID,pName) VALUES (2,'研发部');

INSERT INTO tab_name2(ID,pName) VALUES (2,'平台架构');

INSERT INTO tab_name2(ID,pName) VALUES (3,'研发部');

COMMIT;

期望结果：

ID cName pName 

 1 百度 研发部,市场部

2 Google 研发部

3 网易 研发部,平台架构方法

一:使用wmsys.wm_concat()

SELECT t1.ID,t1.cName,wmsys.wm_concat(t2.pName) FROM tab_name t1,tab_name2 t2 WHERE t1.ID=t2.ID GROUP BY t1.cName,t1.id;

方法二:

使用sys_connect_by_pathselect id, cName, ltrim(max(sys_connect_by_path(pName, ',')), ',') from (select row_number() over(PARTITION by t1.id ORDER by cName) r,t1.*, t2.pName from tab_name t1, tab_name2 t2 where t1.id = t2.id)start with r=1 CONNECT by prior r =r-1 and prior id = id group by id ,cName order by id;

方法三:

使用自定义函数create or replace function coltorow(midId INT) RETURN VARCHAR2 isResult VARCHAR2(1000);beginFOR cur IN (SELECT pName FROM tab_name2 t2 WHERE midId=t2.id) LOOPRESULT:=RESULT||cur.pName||',';END LOOP;RESULT:=rtrim(RESULT,',');return(Result);end coltorow;SELECT t1.*,coltorow(t1.ID) FROM tab_name t1,tab_name2 t2 WHERE t1.ID=t2.ID GROUP BY t1.ID,t1.cname ORDER BY t1.ID;