使用PIVOT和wm_concat 实跟具表数据账期数量的动态表头

pivot 基本语法：pivot（聚合函数 for 列名 in（类型））

wm_concat 基本语法： WM_CONCAT(字段名)

基本需求情况：客户需要在系统中查看某月各项目的回款情况。

之中存在以下问题：每月回款可回前多月的款项。如2019年一月可以回2018年 7月，9月，12月的款。

客户要求可以根据用户回款的月份展示动态表头。以上面的距离是查询2019年1月的回款信息，需要返回的结果7，9，12，分别在三个列上。

原始表结构：

使用PIVOT和wm_concat 实跟具表数据账期数量的动态表头

time_cd 为账期，即回款是回的哪个月。up_month 为操作日期（每月数据录入只能在当月内完成）。depart_id 为部门ID。PRO_ID 为项目ID。HS_NUM,BHS_NUM,SHUIE,分别为含税收入，不含税收入，税额。

目标：取UP_MONTH 为固定的某月。该月下的某一个部门下的所有项目各月的回款统计；

实现思路：使用pivot将 time_cd 转到列上，其中 pivot（聚合函数 for 列名 in（类型））类型部分使用嵌套的方法用 wm_count()函数拼接time_cd。

代码：

SELECT
to_char(
'

SELECT *
from(
SELECT UP_MONTH,DEPART_ID,pro_id,TIME_CD, HS_NUM, BHS_NUM, SHUIE from tprg_dwsr_huikuan where up_month=''201810'' and depart_id = ''100001''
) pivot
(sum(HS_NUM) hs_num，sum(BHS_NUM) BHS_NUM, sum(SHUIE) SHUIE for time_cd in
(
'||(SELECT CASE WHEN (SELECT COUNT (DISTINCT TIME_CD) FROM tprg_dwsr_huikuan WHERE up_month='201810' AND DEPART_ID = '100001')=1 THEN ''''||TO_CLOB(TIME_CD)||''''
            WHEN (SELECT COUNT (DISTINCT TIME_CD) FROM tprg_dwsr_huikuan WHERE up_month='201810' AND DEPART_ID = '100001')>1 THEN ''''||(SELECT REPLACE(wm_concat(DISTINCT TIME_CD),',',''',''')||'''' TIME_CD from tprg_dwsr_huikuan WHERE up_month='201810' AND DEPART_ID = '100001')
              ELSE NULL END TIME_CD_TEXT
FROM 
tprg_dwsr_huikuan WHERE up_month='201810' AND DEPART_ID = '100001' AND ROWNUM=1)||'
 )
)
')
FROM dual

该段代码的查询结果是另一端代码：

使用PIVOT和wm_concat 实跟具表数据账期数量的动态表头

该段代码的查询结果即最初的要求结果：

使用PIVOT和wm_concat 实跟具表数据账期数量的动态表头

最后将代码固化到存储过程中，最初的代码的查询结果放到一个varchar的变量中，执行该变量即返回了数据结果。

后经过优化，存储过程分为两步，第一步处理wm_count()函数处理的部分，将处理结果存放到变量中。第二部处理pivot的部分，引用第一部分处理的结果。

存储过程如下：

CREATE OR REPLACE PROCEDURE PPRG_DWSR_HUIKUAN_INFO
(
P_TIME_CD VARCHAR2,
P_DEPART_ID VARCHAR2,
CUR out sys_refcursor
)
AS
V_TEXT VARCHAR2(500);
V_SQL VARCHAR2(4000);
BEGIN
  
  SELECT wm_concat(AA.TEXT) INTO V_TEXT FROM (
    SELECT DISTINCT ''''||TIME_CD||''' "'||TO_CHAR(TO_NUMBER(SUBSTR(TIME_CD,5,6)))||'"' TEXT,TO_NUMBER(SUBSTR(TIME_CD,5,6)) VALUE
    FROM TPRG_DWSR_HUIKUAN A
    WHERE A.STATE>0 AND A.UP_MONTH=P_TIME_CD AND A.DEPART_ID=P_DEPART_ID
    ORDER BY TO_NUMBER(SUBSTR(TIME_CD,5,6)) ASC
  ) AA;
  
  V_SQL:='SELECT A.ID,A.TYPE_ID,C.ROW_NUM,A.PRO_NAME 维护业务项目,B.PRO_TYPE_NAME 专业类别,
                 CASE WHEN NVL(D.PRO_SL,''0'')=''0'' THEN A.PRO_SL ELSE D.PRO_SL END 税率,
                 D.*,E.HS_SUM 本月合计_含税,E.BHS_SUM 本月合计_不含税,E.SHUIE_SUM 本月合计_税额
          FROM TPRG_DWSR_PROJECT A
          LEFT JOIN TPRG_DWSR_PROJECT_TYPE B
          ON A.TYPE_ID=B.ID
          LEFT JOIN (SELECT Z.TYPE_ID,COUNT(*) ROW_NUM FROM TPRG_DWSR_PROJECT Z WHERE Z.STATE=1 AND Z.IS_HUIKUAN=1 GROUP BY Z.TYPE_ID) C
          ON C.TYPE_ID=A.TYPE_ID
          LEFT JOIN (SELECT * FROM (
              SELECT X.TIME_CD,X.PRO_ID,X.PRO_SL,X.HS_NUM,X.BHS_NUM,X.SHUIE,Y.TEXT_NAME HK_TYPE,X.DSC FROM TPRG_DWSR_HUIKUAN X,TPRG_ZD Y
              WHERE X.STATE>0 AND X.UP_MONTH='''||P_TIME_CD||''' AND X.DEPART_ID='''||P_DEPART_ID||''' AND X.HK_TYPE=Y.VALUE
                    AND Y.TABLE_NAME=''TPRG_DWSR_HUIKUAN'' AND Y.COLUMN_NAME=''HK_TYPE''
              ) PIVOT
              (MIN(HS_NUM) 含税,MIN(BHS_NUM) 不含税, MIN(SHUIE) 税额,MIN(HK_TYPE) 回款属性,MIN(DSC) 备注 FOR TIME_CD IN('||V_TEXT||'))) D
          ON A.ID=D.PRO_ID
          LEFT JOIN(SELECT PRO_ID,SUM(HS_NUM) HS_SUM,SUM(BHS_NUM) BHS_SUM,SUM(SHUIE) SHUIE_SUM 
              FROM TPRG_DWSR_HUIKUAN A
              WHERE STATE>0 AND UP_MONTH='''||P_TIME_CD||''' AND DEPART_ID='''||P_DEPART_ID||'''
              GROUP BY PRO_ID) E
          ON A.ID=E.PRO_ID
          WHERE A.STATE=1 AND A.IS_HUIKUAN=1
          ORDER BY TYPE_ID,ID ASC';

  OPEN CUR FOR
  V_SQL;
END;

使用PIVOT和wm_concat 实跟具表数据账期数量的动态表头

相关推荐