oracle WM_CONCAT行转列

原创

blackproof 2023-07-13 12:32:43 博主文章分类：Oracle小记 ©著作权

文章标签 oracle WM_CONCAT bc SQL 平台架构 文章分类 JavaScript 前端开发

©著作权归作者所有：来自51CTO博客作者blackproof的原创作品，请联系作者获取转载授权，否则将追究法律责任

1. select t.rank, t.Name from

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

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

DEPTNO ENAME
------ ----------
    10 CLARK, KING, MILLER
    20 ADAMS, FORD, JONES, SCOTT, SMITH
    30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

例子如下：

1. SQL> create table idtable (id number,name
2.   
3. Table
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

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

6 rows selected

1. SQL> select id,wmsys.wm_concat(name) name from idtable 2 group by

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

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

        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

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

        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

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

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

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

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

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;