Oracle SQL无聚合PIVOT实现：拆分活跃/非活跃日期列

问题描述 #

现有表结构如下：

需要将数据转换为活跃/非活跃日期分列为单独列的结构，目标格式如下：

尝试过用PIVOT，但它要求必须使用聚合操作，而我不想对日期进行聚合，请问有什么解决办法？

测试数据创建脚本：

CREATE GLOBAL TEMPORARY TABLE my_gtt (
    id         NUMBER,
    dates      VARCHAR2(200),
    is_active  NUMBER
) ON COMMIT PRESERVE ROWS;

INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 15:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 10:00 - 13/07/2023 13:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 13:00 - 13/07/2023 15:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 10:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 18:00 - 13/07/2023 19:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 18:00 - 13/07/2023 19:00', 0);

解决方案 #

可以通过窗口函数生成分组行号，配合PIVOT或条件表达式实现需求，既满足PIVOT的聚合要求，又不会合并原始数据：

方法1：PIVOT结合ROW_NUMBER()

WITH numbered_data AS (
    SELECT 
        id,
        dates,
        is_active,
        -- 按ID和活跃状态分组，给每条记录生成唯一行号
        ROW_NUMBER() OVER (PARTITION BY id, is_active ORDER BY dates) AS rn
    FROM my_gtt
)
SELECT 
    id,
    "0" AS Inactive,
    "1" AS Active
FROM numbered_data
PIVOT (
    MAX(dates) FOR is_active IN (0 AS "0", 1 AS "1")
)
ORDER BY id, rn;

方法2：条件表达式结合全外连接

如果不想用PIVOT，可通过拆分活跃/非活跃数据集后全外连接实现：

WITH inactive_data AS (
    SELECT 
        id,
        dates,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY dates) AS rn
    FROM my_gtt
    WHERE is_active = 0
),
active_data AS (
    SELECT 
        id,
        dates,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY dates) AS rn
    FROM my_gtt
    WHERE is_active = 1
)
SELECT 
    COALESCE(i.id, a.id) AS id,
    i.dates AS Inactive,
    a.dates AS Active
FROM inactive_data i
FULL OUTER JOIN active_data a
    ON i.id = a.id AND i.rn = a.rn
ORDER BY COALESCE(i.id, a.id), COALESCE(i.rn, a.rn);

说明 #

• ROW_NUMBER()的作用是给每个ID下的活跃/非活跃记录分别编号，确保每条记录有唯一标识，这样聚合函数（如MAX）只会返回当前行号对应的唯一日期，不会合并数据。
• 两种方法都能生成目标结构，方法1更简洁，方法2逻辑更直观。

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