Oracle SQL不使用Pivot实现手动转置：补全缺失周数据

问题核心在于原CASE语句仅依赖weekly_test表中已有的数据行，缺失rev_week=3的记录时，无法自动生成对应周的内容。要实现展示1-4所有评审周的问题与答案，缺失周显示“Not Completed”，需要先构建包含所有目标评审周的基础数据集，再与原表关联处理。

解决方案：生成全量周数数据集 + LEFT JOIN关联 #

场景1：列转置（每周作为一列） #

通过CTE生成1-4的所有评审周，再与所有问题做交叉关联，最后左连接原表数据，用COALESCE替换空值：

WITH all_rev_weeks AS (
    SELECT 1 AS rev_week UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4
),
all_questions AS (
    SELECT DISTINCT question FROM weekly_test
)
SELECT
    aq.question,
    MAX(CASE WHEN arw.rev_week = 1 THEN COALESCE(wt.answer, 'Not Completed') END) AS week1_answer,
    MAX(CASE WHEN arw.rev_week = 2 THEN COALESCE(wt.answer, 'Not Completed') END) AS week2_answer,
    MAX(CASE WHEN arw.rev_week = 3 THEN COALESCE(wt.answer, 'Not Completed') END) AS week3_answer,
    MAX(CASE WHEN arw.rev_week = 4 THEN COALESCE(wt.answer, 'Not Completed') END) AS week4_answer
FROM all_rev_weeks arw
CROSS JOIN all_questions aq
LEFT JOIN weekly_test wt
    ON arw.rev_week = wt.rev_week
    AND aq.question = wt.question
GROUP BY aq.question;

场景2：行转置（每周作为一行） #

如果需要每个评审周和问题的组合单独成行，可使用以下SQL：

WITH all_rev_weeks AS (
    SELECT 1 AS rev_week UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4
),
all_questions AS (
    SELECT DISTINCT question FROM weekly_test
)
SELECT
    arw.rev_week,
    aq.question,
    COALESCE(wt.answer, 'Not Completed') AS answer
FROM all_rev_weeks arw
CROSS JOIN all_questions aq
LEFT JOIN weekly_test wt
    ON arw.rev_week = wt.rev_week
    AND aq.question = wt.question
ORDER BY aq.question, arw.rev_week;

关键逻辑说明 #

• all_rev_weeks CTE：生成1-4所有目标评审周，确保即使原表无对应数据，周数仍会被展示。
• all_questions CTE：提取原表中所有唯一问题，保证每个问题都能匹配所有评审周。
• CROSS JOIN：生成问题与评审周的全量组合，避免遗漏任何周/问题配对。
• LEFT JOIN：保留全量组合，原表无对应数据时，answer字段会返回NULL。
• COALESCE：将NULL的answer替换为指定的“Not Completed”文本。

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