Oracle SQL技术问询：无需手动指定日期实现日期行转列

嗨，我明白你的痛点了——静态Pivot必须硬编码日期值，每次日期范围变动都要手动修改，确实很麻烦。针对你统计未来5天客户拣货托盘数的需求，动态SQL是解决这个问题的最佳方案，因为它能自动生成对应日期的列，完全不用手动输入日期字符串。

先梳理下你的核心需求：基于sysdate到sysdate+5的日期范围，把每天的托盘数转成列，按客户分组展示。下面是具体的实现步骤：

1. 静态Pivot的局限 #

你当前的静态Pivot写法需要手动指定IN子句里的日期，比如('02-09-21','02-09-21','02-10-21','02-11-21','02-13-21')，不仅容易出错（比如你这里还重复了日期），而且无法自动适配日期范围的变化。动态SQL可以帮我们自动生成这个部分。

2. 生成动态日期列 #

首先需要生成sysdate到sysdate+5的所有日期（格式化为mm-dd-yy），作为Pivot的列名。我们可以用CONNECT BY生成连续日期，再用LISTAGG拼接成Pivot需要的格式：

SELECT LISTAGG(
         '''' || TO_CHAR(TRUNC(SYSDATE) + LEVEL - 1, 'mm-dd-yy') || ''' AS "' || TO_CHAR(TRUNC(SYSDATE) + LEVEL - 1, 'mm-dd-yy') || '"',
         ', '
       ) AS pivot_cols
FROM DUAL
CONNECT BY LEVEL <= 5;

这段SQL会输出类似：'02-09-21' AS "02-09-21", '02-10-21' AS "02-10-21", ...的字符串，刚好是PivotIN子句需要的内容。

3. 完整的动态SQL实现 #

把上面的日期生成逻辑和你的现有查询结合，用EXECUTE IMMEDIATE执行动态SQL，完整代码如下：

DECLARE
  v_pivot_columns VARCHAR2(1000);
  v_full_sql      VARCHAR2(4000);
BEGIN
  -- 生成动态Pivot列
  SELECT LISTAGG(
           '''' || TO_CHAR(TRUNC(SYSDATE) + LEVEL - 1, 'mm-dd-yy') || ''' AS "' || TO_CHAR(TRUNC(SYSDATE) + LEVEL - 1, 'mm-dd-yy') || '"',
           ', '
         )
    INTO v_pivot_columns
    FROM DUAL
  CONNECT BY LEVEL <= 5;

  -- 构建完整的查询SQL
  v_full_sql := 'WITH pallets AS (
                  SELECT a.comp_code, 
                         a.cust_code, 
                         a.ord_num, 
                         TO_CHAR(a.ord_to_ship_date, ''mm-dd-yy'') ship_date, 
                         SUM(b.ord_ship_qty)/(c.item_qty_bkd_qty) pallets
                  FROM e_ord_h a
                  LEFT JOIN e_ord_d5 b 
                    ON a.comp_code = b.comp_code 
                    AND a.ord_num = b.ord_num
                  LEFT JOIN m_item_d1 c 
                    ON b.comp_code = c.comp_code 
                    AND b.cust_code = c.cust_code 
                    AND b.ord_lev1 = c.item_code
                  WHERE a.comp_code = ''W2''
                    AND c.item_qty_bkd_lev_num = 1
                    AND a.flow_pros_code != ''COOR''
                    AND TRUNC(a.ord_to_ship_date) BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 5
                  GROUP BY a.comp_code, a.cust_code, a.ord_num, a.ord_to_ship_date, c.item_qty_bkd_qty
                ),
                daily_summary AS (
                  SELECT comp_code, 
                         cust_code, 
                         ship_date, 
                         SUM(CEIL(pallets)) pallets
                  FROM pallets
                  GROUP BY comp_code, cust_code, ship_date
                )
                SELECT *
                FROM daily_summary
                PIVOT (SUM(pallets) FOR ship_date IN (' || v_pivot_columns || '))';

  -- 执行动态SQL
  EXECUTE IMMEDIATE v_full_sql;
END;
/

关键优化点说明 #

• 日期截断：我把sysdate改成TRUNC(SYSDATE)，这样可以忽略时间部分，确保日期范围判断准确，不会漏掉当天的订单。
• 避免重复列：动态生成的日期列不会出现你静态代码里的重复日期问题。
• 灵活性：如果未来需要调整日期范围（比如改成7天），只需要把CONNECT BY LEVEL <=5改成<=7即可，完全不用修改其他部分。

执行方式 #

这段代码是PL/SQL块，你可以直接在Oracle SQL Developer、SQL*Plus或者其他支持PL/SQL的工具中运行。如果需要把结果作为查询返回，也可以封装成存储过程，返回一个REF CURSOR供应用程序调用。

这样就能得到你期望的输出，而且完全不用手动输入日期啦！

内容的提问来源于stack exchange，提问作者mrdiu