查询product_name字段时,出现多个数据,为避免多列展示,需要使用||进行拼接,这时可以使用oracle的listagg()函数进行列转行,代码如下:
select cont.ContNo AS policy_no,
substr(cont.SupplierCode, 0, 4) AS supplier_code,
(select listagg((select risk.riskname
from fmrisk risk
where risk.riskcode = f.riskcode),
'||') within GROUP(order by f.riskcode)
from fcpol f
where f.contno = cont.contno) AS product_name,
cont.CValiDate AS effective_date,
(case when cont.state in('01','04') then '1' when cont.state in('02','03') then '2' end) AS main_status,
cont.InsuredName AS insured_name,
cont.InnerContNo AS innerpolicy_no
from fccont cont
使用listagg(字段,'||&#