如何实现多值日期列转置分组？Oracle与Excel方案探讨

场景说明 #

我们正处于遗留系统数据迁移至新系统的初期阶段，现有源数据格式如下：

Name      Date      Award
Roger  2024-06-01     A1
Roger  2024-06-01     A2
Roger  2024-07-01     A3
Alice  2024-06-01     A4
Alice  2024-07-01     A5
Aidan  2024-01-01     A6

期望转换为以下格式：

Name   2024-06-01  2024-06-01  2024-07-01  2024-01-01
Roger       A1         A2          A3          -
Alice       A4          -          A5          -
Aidan       -           -          -           A6

现有问题 #

• 数据行数、转置后列数不固定
• 同一日期下可能存在多个奖项，导致重复日期列
• 使用Oracle PIVOT函数时，因必须聚合（如MAX/MIN）会丢失数据
• 使用Excel Power Query转置时，重复日期会报错，聚合操作同样丢失数据

Oracle SQL 解决方案 #

因为PIVOT要求固定列，而这里列数可变，所以得用动态SQL，同时给每个(Name, Date)组内的奖项加序号，区分同一日期的不同奖项列，避免聚合丢数据。

具体代码 #

DECLARE
  v_cols VARCHAR2(4000);
BEGIN
  -- 动态生成列定义：给每个日期的不同奖项生成对应列
  SELECT LISTAGG(
           'MAX(CASE WHEN rn = ' || rn || ' AND date_col = ''' || date_col || ''' THEN award END) AS "' || date_col || '"',
           ', '
         ) WITHIN GROUP (ORDER BY date_col, rn)
  INTO v_cols
  FROM (
    SELECT DISTINCT 
           date_col,
           ROW_NUMBER() OVER (PARTITION BY date_col ORDER BY award) AS rn
    FROM your_table
  );

  -- 执行动态PIVOT查询
  EXECUTE IMMEDIATE '
    SELECT name, ' || v_cols || '
    FROM (
      SELECT 
        name, 
        date_col, 
        award,
        -- 给每个用户同日期下的奖项加组内序号
        ROW_NUMBER() OVER (PARTITION BY name, date_col ORDER BY award) AS rn
      FROM your_table
    )
    PIVOT (
      MAX(award) FOR (date_col, rn) IN (' || 
        (SELECT LISTAGG('''' || date_col || ''', ' || rn || ''' AS "' || date_col || '"', ', ')
         FROM (
           SELECT DISTINCT 
                  date_col,
                  ROW_NUMBER() OVER (PARTITION BY date_col ORDER BY award) AS rn
           FROM your_table
         )) || ')
    )
    ORDER BY name
  ';
END;
/

逻辑说明 #

1. 先给每个用户的同日期奖项生成组内序号，确保(Name, Date, rn)唯一
2. 动态生成所有需要的列（包括重复日期的多个列）
3. 执行动态PIVOT，空值可后续用NVL函数替换为-

Excel Power Query 解决方案 #

Power Query可以通过添加组内序号，让重复日期下的奖项有唯一标识，避免透视报错和数据丢失，操作更灵活。

具体步骤 #

1. 导入数据：选中源数据表格，点击「数据」>「自表格/区域」，进入Power Query编辑器
2. 添加组内序号：
   • 点击「转换」>「分组依据」，分组字段选Name和Date，新列名设为「组内数据」，操作选「所有行」
   • 点击「组内数据」列的展开按钮，展开所有行
   • 点击「添加列」>「自定义列」，输入公式：Table.AddIndexColumn([组内数据], "序号", 1, 1)
   • 展开自定义列的所有内容，删除多余的「组内数据」列
3. 透视列：
   • 点击「转换」>「透视列」，值列选Award，列名选Date，高级选项选「不要聚合」（已有组内序号，透视时会自动为重复日期生成带后缀的列，可手动重命名去掉后缀）
4. 替换空值：点击「转换」>「替换值」，查找值留空，替换值填-
5. 加载回Excel：点击「关闭并上载」，即可得到目标格式的数据

逻辑说明 #

通过组内序号让每个奖项的(Name, Date, 序号)唯一，透视时不会因为重复日期报错，也不会丢失任何奖项数据，完全适配可变行数和列数的场景。

方案选择建议 #

• 如果数据量较大（十万行以上），优先选Oracle动态SQL，处理效率更高
• 如果需要可视化调整、数据量较小，选Excel Power Query更简单易上手

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