Oracle(18)—— ORACLE中将查询结果的多列汇聚成一行(WMSYS.WM_CONCAT 函数的用法)
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)