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)

 

　　

 

转载于:https://www.cnblogs.com/xushuyi/articles/5367423.html