Oracle 11g动态Pivot未知字段值的统计需求求助

嗨，我太懂你这种烦恼了——静态Pivot硬编码文档类型的方式完全跟不上需求，毕竟谁也没法提前预料所有可能的文档类型，更别说以后还会新增了！在Oracle 11g里要搞定这种动态统计的需求，得靠动态SQL来实现，下面给你一步步拆解可行的方案：

核心思路 #

先自动收集所有唯一的文档类型（就是你用SUBSTR(wdocname,1,5)提取出来的那些值），再把这些类型动态拼成Pivot的列列表，最后执行拼接好的SQL语句，这样就能自动适配所有已存在和未来新增的文档类型啦。

具体实现代码 #

方法1：用PL/SQL生成动态Pivot（推荐，适配大多数场景）

这个方法会自动收集所有文档类型，然后生成并执行动态SQL，直接输出你想要的统计结果：

DECLARE
    v_pivot_cols VARCHAR2(4000); -- 存储拼接好的Pivot列，若类型过多可改用CLOB
    v_sql        VARCHAR2(4000); -- 存储最终的动态SQL语句
    v_result     SYS_REFCURSOR;  -- 用于输出结果的游标
BEGIN
    -- 第一步：收集所有唯一的文档类型，拼成Pivot需要的格式
    SELECT LISTAGG('''' || SUBSTR(wdocname, 1, 5) || ''' AS ' || LOWER(SUBSTR(wdocname, 1, 5)), ', ')
           INTO v_pivot_cols
    FROM (SELECT DISTINCT SUBSTR(wdocname, 1, 5) AS wdocname
          FROM O04T91
          ORDER BY wdocname);

    -- 第二步：拼接完整的动态Pivot SQL
    v_sql := 'SELECT *
              FROM (
                  SELECT shorto04, SUBSTR(wdocname, 1, 5) AS docs
                  FROM O04T91
              )
              PIVOT (
                  COUNT(docs)
                  FOR docs IN (' || v_pivot_cols || ')
              )
              ORDER BY shorto04';

    -- 第三步：执行动态SQL并输出结果（在SQL*Plus/PL/SQL Developer中可直接查看游标结果）
    OPEN v_result FOR v_sql;
    DBMS_SQL.RETURN_RESULT(v_result);
END;
/

处理文档类型过多的情况

如果你的文档类型数量很多，LISTAGG生成的字符串可能会超过VARCHAR2(4000)的长度限制，这时候可以改用XMLAGG来拼接（支持更长的内容）：

DECLARE
    v_pivot_cols CLOB; -- 改用CLOB存储超长的列列表
    v_sql        CLOB;
    v_result     SYS_REFCURSOR;
BEGIN
    SELECT RTRIM(XMLAGG(XMLELEMENT(e, '''' || SUBSTR(wdocname,1,5) || ''' AS ' || LOWER(SUBSTR(wdocname,1,5)), ', ').EXTRACT('//text()') 
                        ORDER BY SUBSTR(wdocname,1,5)).GetClobVal(), ', ')
           INTO v_pivot_cols
    FROM (SELECT DISTINCT SUBSTR(wdocname,1,5) AS wdocname FROM O04T91);

    v_sql := 'SELECT *
              FROM (
                  SELECT shorto04, SUBSTR(wdocname, 1, 5) AS docs
                  FROM O04T91
              )
              PIVOT (
                  COUNT(docs)
                  FOR docs IN (' || v_pivot_cols || ')
              )
              ORDER BY shorto04';

    OPEN v_result FOR v_sql;
    DBMS_SQL.RETURN_RESULT(v_result);
END;
/

注意事项 #

• 确保你有对O04T91表的查询权限，以及执行动态SQL的权限；
• 执行后会自动按shorto04（客户编号）排序，每个客户对应的各类文档数量会清晰展示在对应的列中；
• 以后新增任何文档类型，重新执行这个PL/SQL块就能自动包含新类型的统计，完全不用修改代码！

备注：内容来源于stack exchange，提问作者emare