oracle 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 函数即可以完成 行转列的效果

   
　　转：http://l6259710.iyunv.com/blog/1271915
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代码  

• SQL> create table idtable (id number,name varchar2(30));  
  
•   
  
• Table created  
  
•   
  
• SQL> insert into idtable values(10,'ab');  
  
•   
  
• 1 row inserted  
  
•   
  
• SQL> insert into idtable values(10,'bc');  
  
•   
  
• 1 row inserted  
  
•   
  
• SQL> insert into idtable values(10,'cd');  
  
•   
  
• 1 row inserted  
  
•   
  
• SQL> insert into idtable values(20,'hi');  
  
•   
  
• 1 row inserted  
  
•   
  
• SQL> insert into idtable values(20,'ij');  
  
•   
  
• 1 row inserted  
  
• SQL> insert into idtable values(20,'mn');  
  
•   
  
• 1 row inserted  
  
•   
  
• SQL> select * from idtable;  
  

　　
　　ID NAME
---------- ------------------------------
        10 ab
        10 bc
        10 cd
        20 hi
        20 ij
        20 mn
　　6 rows selected
Sql代码  

• 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代码  

• 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代码  

• 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代码  

• 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代码  

• 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 is
Result VARCHAR2(1000);
begin
FOR cur IN (SELECT pName FROM tab_name2 t2 WHERE midId=t2.id) LOOP
RESULT:=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;