Oracle 實現字串相加 函數

http://lwl0606.cmszs.com/archives/oracle-string-add-function.html

有如下表，實現相同Account 的DSPNAME值相加
      Account DSPNAME
        L1           aa   
        L1           bb   
        L2           cc   
  結果L1          aa,bb

        L2           cc

方法1：在函數中使用cursor

 

CREATE OR REPLACE  FUNCTION SDA.FNROLE (ACC in varchar2) 
    return varchar2 is
  Result varchar2(500); 
  cursor rad_cursor is   
                  select DSPNAME from SDA_USER_ROLE 
                  where LOWER(Account) =LOWER(ACC);
  begin
      for rad_val in rad_cursor 
          loop
            Result:=Result ||','|| rad_val.DSPNAME;   
          end loop;
          RESULT:=SUBSTR(Result,2);
          return(Result);
  end FNROLE;

 

使用select sda.fnrole('1') AS A from dual;

方法2：使用oracle分析函數  轉 自 miclu

City People Make
廣州   1        A
廣州   2        B
廣州   3        C
上海   4        A
上海   5        E
廣州   6        A
上海   7        E

select City,sum(People) as People,ZH_SPLIT(ltrim(max(sys_connect_by_path(Make,',')),','),',') as Make
from (
    select City,People,Make,
       rank()over(order by City) + row_number()over(order by City) RN,
       row_number()over(partition by City order by City) RM
    from SRS_B_CW_TEST
)
start with RM=1
connect by prior RN=RN-1
group by
City

寫一個函數去除重複值
/************************************************************************************
建立者：曾浩
建立時間：2007-9-27
最新修改者：曾浩
最新修改時間：2007-9-27
用途：改進的split函數,
      輸入字串123,123,234,345,234,345,456和字串,
      輸出123,234,345,456
************************************************************************************/
create or replace function ZH_SPLIT(v_string in varchar2, v_delimiter in varchar2)
    return varchar2
is
    j int:=0;
    i int:=1;
    len_string int:=0;
    len_delimiter int:=0;
    str varchar2(4000);
    v_return varchar2(4000);
begin
    len_string := LENGTH(v_string);
    len_delimiter := LENGTH(v_delimiter);
    while j < len_string
    loop
        j := INSTR(v_string, v_delimiter, i);
        if j = 0 then
            j := len_string;
            str := SUBSTR(v_string, i);
            if instr(v_return, str) > 0 then
                null;
            else
                v_return:=v_return||str||',';
            end if;
            if i >= len_string then
                exit;
            end if;
        else
            str := SUBSTR(v_string, i, j - i);
            i := j + len_delimiter;
            if instr(v_return, str) > 0 then
                null;
            else
                v_return:=v_return||str||',';
            end if;
        end if;
    end loop;
    v_return := substr(v_return, 1, length(v_return)-1);
    return v_return;
end;

 方法3：如果ID列為數字，可免去去除重複值的函數

create table TEST AS
(select 1 ID, '11111' MC from dual
union
select 1, '22222'from dual
union
select 2, '11111'from dual
union
select 2, '22222'from dual
union
select 3, '11111'from dual
union
select 3, '22222'from dual
union
select 3, '33333'from dual );

 

select id,ltrim(max(sys_connect_by_path(mc,',')),',') row2col
from (select id,mc,
ID+(row_number() over(order by id)) node_id,
row_number() over(partition by id order by id) rn
from test)
start with rn = 1
connect by node_id-1 = prior node_id
group by id
order by id;

 http://lwl0606.cmszs.com/archives/oracle-string-add-function.html