`
zhuqinglin
  • 浏览: 27603 次
  • 性别: Icon_minigender_1
  • 来自: 郑州
社区版块
存档分类
最新评论

oracle 函数WMSYS.WM_CONCAT 用法(合并行)

 
阅读更多
Sql代码 复制代码 收藏代码
  1. select t.rank, t.Name from t_menu_item t;  
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代码 复制代码 收藏代码
  1. select t.rank, WMSYS.WM_CONCAT(t.NameTIME From t_menu_item t GROUP BY t.rank;  
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;  
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代码 复制代码 收藏代码
  1. SQL> select id,wmsys.wm_concat(namename from idtable 2 group by id;  
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;  
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,namename from idtable;  
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;  
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,namename from idtable;  
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;

分享到:
评论

相关推荐

    重建WMSYS用户的WMSYS.WM_CONCAT函数的3个文件

    WM_CONCAT是一个非标准的Oracle函数,它能将多个字符串合并成一个单一的字符串,用分隔符(默认为逗号)连接。这个函数在早期的Oracle版本中很常用,但在11g及更高版本中被DEPRECATED,推荐使用SQL标准的CONCAT函数...

    ORA-00904 WMSYS.WM_CONCAT标识符无效解决方案

    在Oracle 10g及更早版本中,WMSYS.WM_CONCAT是一个实用的内置函数,允许用户将多个字符串合并成一个单一的字符串,类似于其他数据库系统中的CONCAT或者STRING_AGG函数。然而,由于性能和兼容性的问题,Oracle在11gR2...

    wmsys.wm_concat详细示例.txt

    根据给定文件的信息,我们可以深入探讨`wmsys.wm_concat`的使用方法及其在不同SQL语句中的应用。 ### 基础用法 `wmsys.wm_concat`函数的基本语法如下: ```sql wmsys.wm_concat(column1, column2, ..., columnN) ...

    Oracle 11g 重建WMSYS.WM_CONCAT函数

    Oracle10g之后有些版本已不包含WMSYS.WM_CONCAT函数,若用到此函数, 系统会提示异常:ORA-00904: "WM_CONCAT": invalid identifier 因此需单独重建此函数,方法如下: 解压附件,在sqlplus中执行包里的owmctab....

    类似于wmsys.wm_concat的自定义函数clob版

    在Oracle数据库中,`WMSYS.WM_CONCAT`函数是一个非常实用的工具,它用于将多行数据合并成一个单行字符串。然而,由于其内部处理机制,当需要合并的数据量过大时,可能会遇到“字符串缓冲区太小”的错误。这是因为`WM...

    wmsys_wm_concat函数结果拆解

    在Oracle数据库中,`wmsys.wm_concat`是一个常用的数据处理函数,主要用于连接字符串。它能够接受多个参数,并将这些参数用逗号分隔的形式存储为一个字符串。此函数广泛应用于数据仓库或报表系统中,特别是在需要将...

    oracle行转列聚合函数WMSYS.WM_CONCAT

    ### Oracle行转列聚合函数WMSYS.WM_CONCAT详解 #### 一、概述 在进行数据处理时,经常会遇到需要将多行数据合并为单行的情况,这通常被称为“行转列”。Oracle数据库提供了多种方法来实现这一需求,其中`WMSYS.WM_...

    Oracle重建WMSYS用户及WMSYS.WM_CONCAT函数

    解决ORA-00904: "WMSYS"."WM_CONCAT": 标识符无效 在sqlplus中执行包里的owmctab.plb、owmaggrs.plb、owmaggrb.plb三个脚本即可。 简单来说,用PL/SQL执行下一下几个脚本就可以了。 特别要注意:PL/SQL登录时,要...

    Oracle重建WMSYS用户及WMSYS.WM_CONCAT函数(更低分)

    这个错误通常意味着试图访问或使用`WMSYS.WM_CONCAT`函数时遇到了问题,这是一个在早期Oracle版本中提供的聚合函数,用于字符串连接,但在较新的版本中被弃用并替换为其他更安全和高效的解决方案。 `WMSYS`是Oracle...

    ORA-00904: "WM_CONCAT": 标识符无效

    3. **使用其他数据库功能**:除了LISTAGG,还可以考虑使用XMLAGG、CONNECT_BY_LEVEL等其他Oracle函数,结合转换和处理来达到类似的效果。 4. **查询优化**:在某些情况下,通过改变查询策略,比如使用子查询、连接...

    oracle_wmsys用户

    Oracle中的WMSYS.WM_CONCAT函数是一个自定义聚合函数,用于在分组查询中合并多个字符串值。在某些情况下,可能需要重建这个函数,可能是由于升级、数据库迁移或者遇到与该函数相关的问题。重建过程通常涉及以下步骤...

    oracle—SQL技巧之(二)WMSYS.WM_CONCAT函数实现多行记录用逗号拼接在一起

    总之,`WMSYS.WM_CONCAT`函数在Oracle中提供了一种方便的方式来合并多行数据,但需要谨慎处理其输出长度限制。通过上述方法,我们可以在不超过限制的情况下,有效地展示大量记录的概览,满足用户的需求。不过,需要...

    重建WMSYS用户.zip

    WMSYS.WM_CONCAT是Oracle Spatial中的一个函数,用于处理多行文本数据的连接操作,但在某些情况下可能会出现"标识符无效"的错误。这个问题通常出现在数据库升级、恢复或者对象权限出现问题时。 当您遇到“WMSYS.WM_...

    Oracle10g自定义聚合函数(字符串拼接)

    * 自定义聚合函数 wmsys.wm_concat 替换办法 * 超大字符串拼接,单个字符串4000、分隔符100,可拼出超4000的超长字符串 * 可自定义指定分隔符separator * 可自定义指定排序字段sequence,对于数字或日期类型的...

    Oracle没有WM_CONCAT函数的解决办法.rar

    1、下载三个文件:owmctab.plb 、 owmaggrs.plb 、 owmaggrb.plb ...如果执行结果报错,说找不到WMSYS用户,那么执行 @C:\Users\JOYTRAVEL\Desktop\WMSYS用户\owmctab.plb; 再执行owmaggrb和owmaggrs

    OracleXE中没有WM_CONCAT函数

    包含owmctab.plb 、 owmaggrs.plb 、 owmaggrb.plb 三个文件,可解决OracleXE中没有WM_CONCAT函数的问题;解决方案如下:1、下载三个文件:owmctab.plb 、 owmaggrs.plb 、 owmaggrb.plb 2、用sqlplus登录:...

Global site tag (gtag.js) - Google Analytics