oracle wm concat函数,用于列转行,逗号分隔

               

wm_concat函数

 

首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子,看看这个神奇的函数如何应用

准备测试数据

 

SQL> create table test(id number,name varchar2(20));

SQL> insert into test values(1,'a');

SQL> insert into test values(1,'b');

SQL> insert into test values(1,'c');

SQL> insert into test values(2,'d');

SQL> insert into test values(2,'e');

 

SQL> commit;

 

效果1 : 行转列

 

SQL> select wm_concat(name) from test;

WM_CONCAT(NAME)

-------------------------------------------------------------------------

a,b,c,d,e

 

效果2: 把结果里的逗号替换成"|"

 

SQL> select replace(wm_concat(name),',','|') from test;

REPLACE(WM_CONCAT(NAME),',','|')

-----------------------------------------------------------------------

a|b|c|d|e

 

 

效果3:按ID分组合并name

 

SQL> select id,wm_concat(name) name from test group by id;

ID NAME

---------- ------------------------------

1 a,b,c

2 d,e

 

懒人扩展用法:

案例:我要写一个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠手工写太麻烦了,有没有什么简便的方法? 当然有了,看我如果应用wm_concat来让这个需求变简单

 

SQL> select 'create or replace view as select '|| wm_concat(column_name) || ' from dept'from user_tab_columns where table_name='DEPT';

'CREATEORREPLACEVIEWASSELECT'||WM_CONCAT(COLUMN_NAME)||'FROMDEPT'

--------------------------------------------------------------------------------

create or replace view as select DEPTNO,DNAME,LOC from dept



Oracle9i添加 wm_concat函数(转)


一: 

创建type头 

create or replace type string_sum_obj as object ( 
--聚合函数的实质就是一个对象 
     sum_string varchar2(4000), 
     static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number, 
     --对象初始化 
     member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number, 
     --聚合函数的迭代方法(这是最重要的方法) 
     member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number, 
     --当查询语句并行运行时,才会使用该方法,可将多个并行运行的查询结果聚合 
      
     member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number 
     --终止聚集函数的处理,返回聚集函数处理的结果. 



创建type具体 
create or replace type body string_sum_obj is 
     static function ODCIAggregateInitialize(v_self in out string_sum_obj) return number is 
     begin 
         v_self := string_sum_obj(null); 
         return ODCICONST.Success; 
     end; 
     member function ODCIAggregateIterate(self in out string_sum_obj, value in varchar2) return number is 
     begin 
          /* 连接,解决逗号分隔第一个字母是逗号的问题 */    
           if not self.sum_string is null then 
          self.sum_string := self.sum_string ||','|| value; 
          else 
          self.sum_string := self.sum_string || value; 
          end if; 
          return ODCICONST.Success; 
          /* 最大值 */ 
          if self.sum_string<value then 
              self.sum_string:=value; 
          end if; 
          /* 最小值 */ 
          if self.sum_string>value then 
       self.sum_string:=value;           
          end if; 
           
          return ODCICONST.Success; 
     end; 
     member function ODCIAggregateMerge(self in out string_sum_obj, v_next in string_sum_obj) return number is 
     begin 
          /* 连接 */    
          self.sum_string := self.sum_string || v_next.sum_string; 
          return ODCICONST.Success; 
          /* 最大值 */ 
          if self.sum_string<v_next.sum_string then 
              self.sum_string:=v_next.sum_string; 
          end if; 

          /* 最小值 */ 
          if self.sum_string>v_next.sum_string then 
              self.sum_string:=v_next.sum_string;           
          end if; 
           
          return ODCICONST.Success; 
     end; 
     member function ODCIAggregateTerminate(self in string_sum_obj, return_value out varchar2 ,v_flags in number) return number is 
     begin 
          return_value:= self.sum_string; 
          return ODCICONST.Success; 
     end; 
end; 


创建函数 
create or replace function wm_concat(value Varchar2) return Varchar2 
     parallel_enable aggregate using string_sum_obj; 

二: 

先创建这个类型 

create or replace type strcat_type as object ( 
    cat_string varchar2(4000), 
    static function ODCIAggregateInitialize(cs_ctx In Out strcat_type) return number, 
    member function ODCIAggregateIterate(self In Out strcat_type,value in varchar2) return number, 
    member function ODCIAggregateMerge(self In Out strcat_type,ctx2 In Out strcat_type) return number, 
    member function ODCIAggregateTerminate(self In Out strcat_type,returnValue Out varchar2,flags in number) return number 


缺少类型体内容: 

.... 


然后创建这个函数 

CREATE OR REPLACE FUNCTION strcat(input varchar2 ) 
RETURN varchar2 
PARALLEL_ENABLE AGGREGATE USING strcat_type; 


几个用法: 

9i: SYS_CONNECT_BY_PATH 函数 



SQL> select id, replace(wmsys.wm_concat(rmak), ',', '') from test group by id; 

        ID REPLACE(WMSYS.WM_CONCAT(RMAK), 
---------- -------------------------------------------------------------------------------- 
         9 timggg 
        21 littlefff 
        23 tom 



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


           

再分享一下我老师大神的人工智能教程吧。零基础!通俗易懂!风趣幽默!还带黄段子!希望你也加入到我们人工智能的队伍中来!https://blog.csdn.net/jiangjunshow

  • 1
    点赞
  • 0
    评论
  • 2
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值