Oracle 19c中如何使用Pivot将日期行转换为列

注意：你提供的原查询生成的是倒序日期，先修正为生成正序连续日期的基础查询：

SELECT
  TO_CHAR((TO_DATE('01-04-2024','DD-MM-YYYY') + LEVEL - 1), 'DD-MM-YYYY') AS day
FROM
  dual
CONNECT BY LEVEL <= (TO_DATE('01-05-2024','DD-MM-YYYY') - TO_DATE('01-04-2024','DD-MM-YYYY') + 1);

一、静态Pivot（固定日期范围） #

如果日期范围固定，可以手动指定列名，直接使用Pivot语法转换：

列名为Day1/Day2格式 #

WITH date_rows AS (
  -- 生成正序日期及对应的列标识
  SELECT
    TO_CHAR((TO_DATE('01-04-2024','DD-MM-YYYY') + LEVEL - 1), 'DD-MM-YYYY') AS day,
    'Day' || LEVEL AS col_name
  FROM dual
  CONNECT BY LEVEL <= (TO_DATE('01-05-2024','DD-MM-YYYY') - TO_DATE('01-04-2024','DD-MM-YYYY') + 1)
)
SELECT *
FROM date_rows
PIVOT (
  -- 用MAX聚合，每个col_name对应唯一日期，MAX/MIN结果一致
  MAX(day) FOR col_name IN (
    'Day1' AS Day1, 'Day2' AS Day2, 'Day3' AS Day3,
    -- 按实际日期范围补充剩余列，例如到Day31
    'Day31' AS Day31
  )
);

列名为日期字符串格式 #

如果希望列名直接显示日期，可修改如下：

WITH date_rows AS (
  SELECT
    TO_CHAR((TO_DATE('01-04-2024','DD-MM-YYYY') + LEVEL - 1), 'DD-MM-YYYY') AS day,
    TO_CHAR((TO_DATE('01-04-2024','DD-MM-YYYY') + LEVEL - 1), 'DD-MM-YYYY') AS col_name
  FROM dual
  CONNECT BY LEVEL <= (TO_DATE('01-05-2024','DD-MM-YYYY') - TO_DATE('01-04-2024','DD-MM-YYYY') + 1)
)
SELECT *
FROM date_rows
PIVOT (
  MAX(day) FOR col_name IN (
    '01-04-2024' AS "01-04-2024", '02-04-2024' AS "02-04-2024",
    -- 补充剩余日期列
    '01-05-2024' AS "01-05-2024"
  )
);

二、动态Pivot（日期为参数） #

如果日期是动态参数（范围不固定），需要用动态SQL自动生成列名列表，示例如下：

列名为Day1/Day2格式 #

DECLARE
  v_start_date DATE := TO_DATE('01-04-2024','DD-MM-YYYY'); -- 起始日期参数
  v_end_date DATE := TO_DATE('01-05-2024','DD-MM-YYYY');   -- 结束日期参数
  v_col_list VARCHAR2(4000); -- 存储生成的列名列表
  v_sql VARCHAR2(4000);      -- 动态SQL语句
BEGIN
  -- 生成Pivot需要的列名格式：'Day1' AS Day1, 'Day2' AS Day2...
  SELECT LISTAGG('''' || 'Day' || LEVEL || ''' AS Day' || LEVEL, ', ') WITHIN GROUP (ORDER BY LEVEL)
  INTO v_col_list
  FROM dual
  CONNECT BY LEVEL <= (v_end_date - v_start_date + 1);

  -- 构建完整动态SQL
  v_sql := '
    WITH date_rows AS (
      SELECT
        TO_CHAR((:start_date + LEVEL - 1), ''DD-MM-YYYY'') AS day,
        ''Day'' || LEVEL AS col_name
      FROM dual
      CONNECT BY LEVEL <= (:end_date - :start_date + 1)
    )
    SELECT *
    FROM date_rows
    PIVOT (
      MAX(day) FOR col_name IN (' || v_col_list || ')
    )';

  -- 执行动态SQL并绑定参数
  EXECUTE IMMEDIATE v_sql USING v_start_date, v_end_date, v_start_date;
END;
/

列名为日期字符串格式 #

若要以日期字符串作为列名，修改列名生成逻辑即可：

DECLARE
  v_start_date DATE := TO_DATE('01-04-2024','DD-MM-YYYY');
  v_end_date DATE := TO_DATE('01-05-2024','DD-MM-YYYY');
  v_col_list VARCHAR2(4000);
  v_sql VARCHAR2(4000);
BEGIN
  -- 生成日期字符串格式的列名
  SELECT LISTAGG('''' || TO_CHAR(v_start_date + LEVEL - 1, 'DD-MM-YYYY') || ''' AS "' || TO_CHAR(v_start_date + LEVEL - 1, 'DD-MM-YYYY') || '"', ', ') WITHIN GROUP (ORDER BY LEVEL)
  INTO v_col_list
  FROM dual
  CONNECT BY LEVEL <= (v_end_date - v_start_date + 1);

  v_sql := '
    WITH date_rows AS (
      SELECT
        TO_CHAR((:start_date + LEVEL - 1), ''DD-MM-YYYY'') AS day,
        TO_CHAR((:start_date + LEVEL - 1), ''DD-MM-YYYY'') AS col_name
      FROM dual
      CONNECT BY LEVEL <= (:end_date - :start_date + 1)
    )
    SELECT *
    FROM date_rows
    PIVOT (
      MAX(day) FOR col_name IN (' || v_col_list || ')
    )';

  EXECUTE IMMEDIATE v_sql USING v_start_date, v_end_date, v_start_date;
END;
/

关键说明 #

• Oracle的Pivot语法必须配合聚合函数（如MAX/MIN），因为Pivot本质是聚合转换，这里每个列标识对应唯一日期，所以用MAX或MIN结果一致。
• 动态SQL适用于日期范围不固定的场景，静态Pivot适合范围固定的场景，操作更简单。

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