WMSYS.WM_CONCAT 函數的用法

原创 2011年01月19日 10:34:00

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

 

 

 

 

 select dm, mc, wmsys.wm_concat(xd) xd,wmsys.wm_concat(mm) mm
  from (select a.dm, a.mc, b.xd,c.mc mm
          from jyj_xx a, jyj_xx_xz b,zd_xxlx c
         where a.id = b.zbid and b.xd=c.dm
         order by b.xd)
 group by dm, mc;

wmsys_wm_concat函数结果拆解

  • 2010年12月25日 02:25
  • 401B
  • 下载

[oracle]百思不得其解:wmsys.wm_concat的用法与数据库版本的问题

[quote=引用 楼主 lemonran 的回复:] oracle10g以上版本提供行转列组合成字符串函数wmsys.wm_concat   例如有个users表如下:   id yhm xm   ...

WMSYS.WM_CONCAT的用法 行列转置

select t.rank, t.Name from t_menu_item t;     10 CLARK     10 KING     10 MILLER     20 ADAMS ...

使用Listagg分析函数优化wmsys.wm_concat

在上周末优化班的时候一个朋友拿了一个SQL出来,让我现场优化,因为当时太忙,我安排七年老师帮忙处理。跑得慢的SQL如下:with temp as                 (select sgd...

Oracle内部函数 wmsys.wm_concat 替换办法及思考

如果你不知道这个函数没有关系,因为您可以有其他的办法来实现。如果你已经在使用这个函数,一定要注意。 wmsys.wm_concat 是ORACLE内部函数,没有对外公布,也就是说,你可以使用...
  • eric_za
  • eric_za
  • 2014年09月20日 17:54
  • 3257

oracle中WMSYS.WM_CONCAT函数的版本差异

昨天在测试的时候发现,开发人员写的一段程序放在开发库中是好的,但是放在测试库中就会有问题。开发人员一直找不到问题的原因在哪里。于是就花了点时间协助开发人员来找问题的根本原因。      通过...
  • eric_za
  • eric_za
  • 2014年08月01日 09:00
  • 382

关于ORACLE wmsys.wm_concat 行转列函数使用

转自http://blog.csdn.net/pengdingxu10/article/details/46438567 这两天在项目中遇到了一个问题,就是系统中使用了wmsys.wm_conc...

自定义函数替代WMSYS.WM_CONCAT

参考文章: http://docs.oracle.com/cd/B13789_01/appdev.101/b10800/dciaggfns.htm#i1005028 http://www.eygl...

oracle 列转行函数 WMSYS.WM_CONCAT 排序不规则处理

源自:http://www.tuicool.com/articles/mmiAJf   oracle 列转行函数 WMSYS.WM_CONCAT  排序不规则处理 时间 2013-10-10 1...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:WMSYS.WM_CONCAT 函數的用法
举报原因:
原因补充:

(最多只允许输入30个字)