字符串拼接列转行

当前页面中仅仅显示物品的价格信息

代码如下所示：
发现没有数据
SELECT
(SELECT wm_concat(dj) FROM CG_CGLXMXB LXMX WHERE XMBH = LXMXB.XMBH AND LXMX.XFKS = ‘6423B8ACD0368AFCE050A8C0D90A1950’) QTJG
FROM CG_CGLXB LX
LEFT JOIN CG_CGHTB HT ON HT.XMBH = LX.XMBH
LEFT JOIN CG_XMMCB XMMC ON XMMC.GID = LX.XMMC
LEFT JOIN OA_SYS_DWB DW ON DW.DWBH = LX.SQDW
LEFT JOIN OA_SYS_DWB SJDW ON SJDW.DWBH = DW.SJDW
LEFT JOIN (SELECT XMBH, SUM(SHL) ZTS, SUM(ZJ) ZJE, WM_CONCAT(DISTINCT KS.XFKS) DHXL
FROM CG_CGLXMXB MX
LEFT JOIN HQ_KS KS ON KS.GID = MX.XFKS GROUP BY XMBH) LXMXB ON LXMXB.XMBH = LX.XMBH
LEFT JOIN CG_CGYSB YS ON YS.XMBH = LX.XMBH
LEFT JOIN CG_GYSB GYS
ON GYS.GYSBH = YS.QY WHERE LX.ZTBZ = ‘11’
AND DW.SCHOOL = ‘SCHOOL’
现在需要做的功能是需要将将字符串拼接并且列转行
代码如下

SELECT
(SELECT to_char(wm_concat(wpmc||’(’||dj||’)’) ) FROM CG_CGLXMXB LXMX WHERE XMBH = LXMXB.XMBH AND LXMX.XFKS = ‘6423B8ACD0368AFCE050A8C0D90A1950’) QTJG
FROM CG_CGLXB LX
LEFT JOIN CG_CGHTB HT ON HT.XMBH = LX.XMBH
LEFT JOIN CG_XMMCB XMMC ON XMMC.GID = LX.XMMC
LEFT JOIN OA_SYS_DWB DW ON DW.DWBH = LX.SQDW
LEFT JOIN OA_SYS_DWB SJDW ON SJDW.DWBH = DW.SJDW
LEFT JOIN (SELECT XMBH, SUM(SHL) ZTS, SUM(ZJ) ZJE, WM_CONCAT(DISTINCT KS.XFKS) DHXL
FROM CG_CGLXMXB MX
LEFT JOIN HQ_KS KS ON KS.GID = MX.XFKS GROUP BY XMBH) LXMXB ON LXMXB.XMBH = LX.XMBH
LEFT JOIN CG_CGYSB YS ON YS.XMBH = LX.XMBH
LEFT JOIN CG_GYSB GYS
ON GYS.GYSBH = YS.QY WHERE LX.ZTBZ = ‘11’
AND DW.SCHOOL = ‘SCHOOL’

在此基础上添加了 to_char(wm_concat(to_char(wpmc)||’(’||dj||’)’) )
有了相对应的数据

代码如下：
SELECT
(SELECT to_char(wm_concat(to_char(wpmc)||’(’||dj||’)’) ) FROM CG_CGLXMXB LXMX WHERE XMBH = LXMXB.XMBH AND LXMX.XFKS = ‘6423B8ACD0368AFCE050A8C0D90A1950’) QTJG
FROM CG_CGLXB LX
LEFT JOIN CG_CGHTB HT ON HT.XMBH = LX.XMBH
LEFT JOIN CG_XMMCB XMMC ON XMMC.GID = LX.XMMC
LEFT JOIN OA_SYS_DWB DW ON DW.DWBH = LX.SQDW
LEFT JOIN OA_SYS_DWB SJDW ON SJDW.DWBH = DW.SJDW
LEFT JOIN (SELECT XMBH, SUM(SHL) ZTS, SUM(ZJ) ZJE, WM_CONCAT(DISTINCT KS.XFKS) DHXL
FROM CG_CGLXMXB MX
LEFT JOIN HQ_KS KS ON KS.GID = MX.XFKS GROUP BY XMBH) LXMXB ON LXMXB.XMBH = LX.XMBH
LEFT JOIN CG_CGYSB YS ON YS.XMBH = LX.XMBH
LEFT JOIN CG_GYSB GYS
ON GYS.GYSBH = YS.QY WHERE LX.ZTBZ = ‘11’
AND DW.SCHOOL = ‘SCHOOL’

最后一步是替换掉所有的逗号

replace(to_char(wm_concat(to_char(wpmc) || ‘(’ || dj || ‘元)’)),’,’,’;’)

学习的转载连接
https://www.cnblogs.com/qianyuliang/p/6649983.html