Oracle中如何将2列表的行数据转换为固定4列的结果集

实现SQL #

WITH original_table AS (
    -- 此处为模拟原始数据，实际使用时替换为你自己的表即可
    SELECT 1 Column1, 'N' Column2 FROM dual UNION ALL
    SELECT 2 Column1, 'N' Column2 FROM dual UNION ALL
    SELECT 3 Column1, 'N' Column2 FROM dual UNION ALL
    SELECT 4 Column1, 'N' Column2 FROM dual UNION ALL
    SELECT 5 Column1, 'N' Column2 FROM dual
),
ranked_data AS (
    SELECT 
        Column1,
        ROW_NUMBER() OVER(ORDER BY Column1) rn,
        CEIL(ROW_NUMBER() OVER(ORDER BY Column1)/4) group_id,
        MOD(ROW_NUMBER() OVER(ORDER BY Column1)-1,4) + 1 pos
    FROM original_table
),
pivoted_data AS (
    SELECT
        group_id,
        NVL(MAX(CASE WHEN pos = 1 THEN TO_CHAR(Column1) END), 'N') col1,
        NVL(MAX(CASE WHEN pos = 2 THEN TO_CHAR(Column1) END), 'N') col2,
        NVL(MAX(CASE WHEN pos = 3 THEN TO_CHAR(Column1) END), 'N') col3,
        NVL(MAX(CASE WHEN pos = 4 THEN TO_CHAR(Column1) END), 'N') col4
    FROM ranked_data
    GROUP BY group_id
)
-- 合并转置结果与要求的第三行占位数据
SELECT col1,col2,col3,col4 FROM pivoted_data
UNION ALL
SELECT 'N','N',NULL,NULL FROM dual
ORDER BY group_id NULLS LAST;

逻辑说明 #

• 先用ROW_NUMBER()窗口函数对原始数据按Column1升序排序生成连续行号
• 通过CEIL(行号/4)计算组号，实现每4行划分为同一组的规则
• 通过MOD(行号-1,4)+1计算每行在对应组内的位置，取值固定为1到4
• 配合CASE判断+聚合函数MAX按组完成转置，用NVL函数将组内空缺位置的空值替换为N
• 最后用UNION ALL补全要求的第三行占位数据，排序时将无组号的第三行放在末尾即可

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