select wm_concat(colname) from tablename where kind='2'
--wm_concat未公开函数10g及以上版本可以使用 --9i可使用以下语句来实现 select ltrim(max(sys_connect_by_path(colname,',')),',') from (select rownum rn, colname from tablename where kind='2') start with rn=1 connect by prior rn=rn-1;
聚合字符串,有很多种解法,比较简单的是直接使用wm_concat,不过这个是有字符串长度限制的
可能我没有表达的太清楚,并不是简单的聚合字符串,在举个例子吧select @Result=REPLACE(upper(@Result),upper(oldValue),isnull(newValue,'')) from bm where UPPER(bm)=UPPER(@sllb) order by pydm 类似与这样我修改成 select replace(upper(f_Result),upper(oldValue),nvl(newValue,'')) into f_Result from bm_slyxxl_bz where UPPER(bm0000)=UPPER(f_sllb) order by pydm; 在Oracle中是行不通的,就是遍历符合条件的行,之后赋值之类的
SQL> select name from test where id=10;
NAME -------------------------------------------------------------------------------- change change
SQL> select wm_concat(name) from test where id=10;
select wm_concat(colname)
from tablename
where kind='2'
--9i可使用以下语句来实现
select ltrim(max(sys_connect_by_path(colname,',')),',') from
(select rownum rn, colname from tablename where kind='2')
start with rn=1 connect by prior rn=rn-1;
类似与这样我修改成 select replace(upper(f_Result),upper(oldValue),nvl(newValue,'')) into f_Result from bm_slyxxl_bz where UPPER(bm0000)=UPPER(f_sllb) order by pydm; 在Oracle中是行不通的,就是遍历符合条件的行,之后赋值之类的
NAME
--------------------------------------------------------------------------------
change
change
SQL> select wm_concat(name) from test where id=10;
WM_CONCAT(NAME)
--------------------------------------------------------------------------------
change,change
SQL> var var1 char
SQL> print var1
var1
---------
SQL> begin
2 select wm_concat(name) into :var1 from (select name from test where id=10);
3 end;
4 /
PL/SQL procedure successfully completed
var1
---------
change,change
SQL> print var1
var1
---------
change,change