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;