WMSYS.WM_CONCAT 函数的用法:

引用路径：http://blog.sina.com.cn/s/blog_5c4736800100l2qw.html

 

详例sql脚本：    select b.bantchno,       c.codename,       cc.codename,       b.sendstartdate,       b.sendenddate,       b.planstartdate,       b.palnenddate,       (select wmsys.wm_concat(to_char(b1.planchildstartdate, 'yyyy-mm-dd') || '至' ||                               to_char(b1.palnchildenddate, 'yyyy-mm-dd')) condate          from bantchnosubmanage b1         where '1460001220000' = '1460001220000'           and b1.bantchno = b.bantchno),           (select p.pdate from ((select bantchno, ltrim(max(sys_connect_by_path(pName, ',')), ',') pdate from (select row_number() over(PARTITION by t1.bantchno ORDER by t1.bantchno) r,t1.*, (to_char(t2.planchildstartdate,'yyyy-MM-dd')||'至'||to_char(t2.palnchildenddate,'yyyy-MM-dd')) pName from bantchnomanage t1, bantchnosubmanage t2 where t1.bantchno = t2.bantchno order by t1.bantchno,t2.planchildstartdate )start with r=1 CONNECT by prior r =r-1 and prior bantchno = bantchno group by bantchno order by bantchno)) p where p.bantchno = b.bantchno),       b.remark  from bantchnomanage b  left join codemanage c    on c.codetype = 'MyState'   and c.codecode = b.status  left join codemanage cc    on cc.codetype = 'BooleanFlag'   and cc.codecode = b.temp1 where 1 = 1   and b.temp2 = '1' order by b.bantchno desc;

 

方案一、

select wmsys.wm_concat(to_char(b1.planchildstartdate, 'yyyy-mm-dd') || '至' ||

to_char(b1.palnchildenddate, 'yyyy-mm-dd')) condate

from bantchnosubmanage b1

where '1460001220000' = '1460001220000'

and b1.bantchno = b.bantchno

 

方案二、

select bantchno, ltrim(max(sys_connect_by_path(pName, ',')), ',') pdate

from (select row_number() over(PARTITION by t1.bantchno ORDER by t1.bantchno) r,t1.*, (to_char(t2.planchildstartdate,'yyyy-MM-dd')||'至'||to_char(t2.palnchildenddate,'yyyy-MM-dd')) pName from bantchnomanage t1, bantchnosubmanage t2 where t1.bantchno = t2.bantchno order by ....)

start with r=1 CONNECT by prior r =r-1 and prior bantchno = bantchno group by bantchno order by bantchno

方案三、

(WITH Q AS

(

SELECT bantchno key,(to_char(planchildstartdate,'yyyy-MM-dd')||'至'||to_char(palnchildenddate,'yyyy-MM-dd')) x FROM bantchnosubmanage

)

SELECT key,

RTRIM(

XMLAGG (XMLELEMENT(e, x||',') ORDER BY x).EXTRACT('//text()')

) AS concatval

FROM q 

GROUP BY key)