oracle WM_CONCAT行转列 博客分类： Oracle小记 oracleWM_CONCAT

 

 

Sql代码  

1. 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.iteye.com/blog/1271915

Sql代码  

1. 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代码  

1. SQL> create table idtable (id number,name varchar2(30));  
2.   
3. Table created  
4.   
5. SQL> insert into idtable values(10,'ab');  
6.   
7. 1 row inserted  
8.   
9. SQL> insert into idtable values(10,'bc');  
10.   
11. 1 row inserted  
12.   
13. SQL> insert into idtable values(10,'cd');  
14.   
15. 1 row inserted  
16.   
17. SQL> insert into idtable values(20,'hi');  
18.   
19. 1 row inserted  
20.   
21. SQL> insert into idtable values(20,'ij');  
22.   
23. 1 row inserted  
24. SQL> insert into idtable values(20,'mn');  
25.   
26. 1 row inserted  
27.   
28. SQL> select * from idtable;  

 

        ID NAME
---------- ------------------------------
        10 ab
        10 bc
        10 cd
        20 hi
        20 ij
        20 mn

6 rows selected

Sql代码  

1. 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代码  

1. 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代码  

1. 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代码  

1. 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代码  

1. 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;