逗号隔开‘,‘
select LISTAGG(SUBSTR(TO_CHAR(FREESTARTTIME,‘YYYY-MM-DD HH24:MI:SS‘),11,9) ||‘~‘||trim(SUBSTR(TO_CHAR(FREEENDTIME,‘YYYY-MM-DD HH24:MI:SS‘),11,9)),‘,‘)WITHIN GROUP (ORDER BY TPF.FREEID) AS FREETIMESPAN FROM TRA_PRICINGBERTH TPB LEFT JOIN TRA_PRICINGFREE TPF ON TPB.PRICINGSTRATEGYID = TPF.PRICINGSTRATEGYID WHERE TPB.BERTHCODE=‘108211‘ AND TPF.FREEDATETYPE=‘1‘ SELECT LISTAGG( (SBPT.PARKSTIME||‘~‘||SBPT.PARKETIME),‘,‘) WITHIN GROUP (ORDER BY SBPT.PARKTIMEID) AS SAREE FROM SYS_BERTHANDTYPE SBT LEFT JOIN SYS_BERTHTYPEPARKTIME SBPT ON SBT.BERTHTYPEID = SBPT.BERTHTYPEID WHERE SBT.BERTHCODE=‘108211‘ AND SBPT.DATETYPE = 1 AND SBPT.ISALLOW =2 AS NOTALLOWTIMESPAN
20:00:00~23:59:59, 00:00:00~07:29:00
分号隔开‘;‘ 用trim实现去空格功能
select LISTAGG(SUBSTR(TO_CHAR(FREESTARTTIME,‘YYYY-MM-DD HH24:MI:SS‘),11,9) ||‘~‘||trim(SUBSTR(TO_CHAR(FREEENDTIME,‘YYYY-MM-DD HH24:MI:SS‘),11,9)),‘;‘)WITHIN GROUP (ORDER BY TPF.FREEID) AS FREETIMESPAN FROM TRA_PRICINGBERTH TPB LEFT JOIN TRA_PRICINGFREE TPF ON TPB.PRICINGSTRATEGYID = TPF.PRICINGSTRATEGYID WHERE TPB.BERTHCODE=‘108211‘ AND TPF.FREEDATETYPE=‘1‘ SELECT LISTAGG( (SBPT.PARKSTIME||‘~‘||SBPT.PARKETIME),‘;‘) WITHIN GROUP (ORDER BY SBPT.PARKTIMEID) AS SAREE FROM SYS_BERTHANDTYPE SBT LEFT JOIN SYS_BERTHTYPEPARKTIME SBPT ON SBT.BERTHTYPEID = SBPT.BERTHTYPEID WHERE SBT.BERTHCODE=‘108211‘ AND SBPT.DATETYPE = 1 AND SBPT.ISALLOW =2 AS NOTALLOWTIMESPAN
20:00:00~23:59:59;00:00:00~07:29:00
原文：http://www.cnblogs.com/Warmsunshine/p/4573395.html