Oracle資料庫合并行記錄，WMSYS.WM_CONCAT 函數的用法

標籤：

Sql代碼

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 函數即可以完成行轉列的效果

Sql代碼

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 idtable 2 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_path

select 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;

本文轉自：http://l6259710.iteye.com/blog/1271915

Oracle資料庫合并行記錄，WMSYS.WM_CONCAT 函數的用法