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
create or replace function getStrByList(fgf in varchar2,tab_name in varchar2,hzx in
varchar2,hbwbx in varchar2,hzxcsz in varchar2) return string
----功能描述:oracle多行合并成一行
----参数描述:
----fgf:分隔符,对组合形成的数据中间插入分隔符
----tab_name:查询的数据源表
----hzx:汇总项字段
----hbwbx:合并文本项
----hzxcsz:汇总项参数值
is
sql_str varchar2(1024);
result_val varchar2(1024);
begin
sql_str:='SELECT TRANSLATE (LTRIM (text,''@''),''*@'',''*'||fgf||''') researcherList FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,lvl DESC) rn,n_sec_code,
text FROM (SELECT n_sec_code, LEVEL lvl,SYS_CONNECT_BY_PATH (c_researcher_code,''@'') text FROM
(SELECT '||hzx||' as n_sec_code, '||hbwbx||' as c_researcher_code,ROW_NUMBER() OVER(PARTITION BY
'||hzx||' ORDER BY '||hzx||','||hbwbx||') x FROM '||tab_name||' where '||hzx||'='''||hzxcsz||''')
a CONNECT BY n_sec_code = PRIOR n_sec_code AND x - 1 = PRIOR x)) WHERE rn=1 ORDER BY
n_sec_code';
EXECute IMMEDIATE sql_str INTO result_val;
return result_val;
end getStrByList;
**********本博客所有内容均为原创,如有转载请注明作者和出处!!!**********
Name: guoyJoe
QQ: 252803295
Email: oracledba_cn@hotmail.com
Blog: http://blog.csdn.net/guoyJoe
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM: http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题:哪些SGA结构是必需的,哪些是可选的?否则拒绝申请!!!
答案在:http://blog.csdn.net/guoyjoe/article/details/8624392
Oracle@Paradise 总群:127149411
Oracle@Paradise No.1群:177089463(已满)
Oracle@Paradise No.2群:121341761
Oracle@Paradise No.3群:140856036
转载于:https://blog.51cto.com/guoyjoe/1429046