逗号隔开','
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=''
AND TPF.FREEDATETYPE=''
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='' 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=''
AND TPF.FREEDATETYPE=''
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='' AND SBPT.DATETYPE = 1 AND SBPT.ISALLOW =2
AS NOTALLOWTIMESPAN
20:00:00~23:59:59;00:00:00~07:29:00