如何使用Oracle函数将季度分数宽表查询结果转换为窄表？

你可以通过两种常用方式将现有宽表转换为目标窄表格式：

方法一：使用UNION ALL（通用兼容方案） #

这是所有关系型数据库都支持的通用写法，将每行数据拆分为两行，分别映射过去季度和当前季度的分数与季度信息：

-- 基于原查询结果转换为窄表
SELECT past_score AS SCORE, past_quarter AS Quarter
FROM (
    -- 原有的宽表查询语句
    SELECT Greatest(1, Least(5, score))      AS past_score,
           Greatest(1, Least(5, curr_score)) AS current_score,
           current_quarter,
           past_quarter
    FROM   (SELECT Round(( 0.4 * normalized_sick_leave ) +
                         ( 0.3 * normalized_total ) + (
                                0.3 *
                                        overall_rating ), 1) AS score,
                   Round(( 0.4 * normalized_sick_leave_current ) + ( 0.3 *
                         normalized_total_curr )
                         + (
                         0.3 * overall_rating_curr ), 1)     AS curr_score,
                   current_quarter,
                   past_quarter
            FROM   (SELECT Nvl(abs_record.duration / 10, 0)                  AS
                                   normalized_sick_leave,
                           time_entry.hours / 540                            AS
                           normalized_total,
                           5 - To_number(perf_rating.overall_rating)         AS
                           overall_rating,
                           abs_record_current.duration / 10                  AS
                                   normalized_sick_leave_current,
                           time_entry_current.hours / 540                    AS
                                   normalized_total_curr,
                           5 - To_number(perf_rating_current.overall_rating) AS
                           overall_rating_curr
                                   ,
                           time_entry.quarter
                                   past_quarter,
                           time_entry_current.quarter
                           CURRENT_QUARTER
                    FROM   abs_record ABS_RECORD,
                           time_entry TIME_ENTRY,
                           perf_rating perf_rating,
                           abs_record_current ABS_RECORD_current,
                           time_entry_current TIME_ENTRY_current,
                           perf_rating_current perf_rating_current
                    WHERE  abs_record.person_id(+) = time_entry.person_id
                           AND time_entry.person_id = perf_rating.person_id(+)
                           AND abs_record_current.person_id(+) =
                               time_entry_current.person_id
                           AND time_entry_current.person_id =
                               perf_rating_current.person_id(+)
                           AND time_entry_current.person_id (+) =
                               time_entry.person_id))
UNION ALL
SELECT current_score AS SCORE, current_quarter AS Quarter
FROM (
    -- 原有的宽表查询语句
    SELECT Greatest(1, Least(5, score))      AS past_score,
           Greatest(1, Least(5, curr_score)) AS current_score,
           current_quarter,
           past_quarter
    FROM   (SELECT Round(( 0.4 * normalized_sick_leave ) +
                         ( 0.3 * normalized_total ) + (
                                0.3 *
                                        overall_rating ), 1) AS score,
                   Round(( 0.4 * normalized_sick_leave_current ) + ( 0.3 *
                         normalized_total_curr )
                         + (
                         0.3 * overall_rating_curr ), 1)     AS curr_score,
                   current_quarter,
                   past_quarter
            FROM   (SELECT Nvl(abs_record.duration / 10, 0)                  AS
                                   normalized_sick_leave,
                           time_entry.hours / 540                            AS
                           normalized_total,
                           5 - To_number(perf_rating.overall_rating)         AS
                           overall_rating,
                           abs_record_current.duration / 10                  AS
                                   normalized_sick_leave_current,
                           time_entry_current.hours / 540                    AS
                                   normalized_total_curr,
                           5 - To_number(perf_rating_current.overall_rating) AS
                           overall_rating_curr
                                   ,
                           time_entry.quarter
                                   past_quarter,
                           time_entry_current.quarter
                           CURRENT_QUARTER
                    FROM   abs_record ABS_RECORD,
                           time_entry TIME_ENTRY,
                           perf_rating perf_rating,
                           abs_record_current ABS_RECORD_current,
                           time_entry_current TIME_ENTRY_current,
                           perf_rating_current perf_rating_current
                    WHERE  abs_record.person_id(+) = time_entry.person_id
                           AND time_entry.person_id = perf_rating.person_id(+)
                           AND abs_record_current.person_id(+) =
                               time_entry_current.person_id
                           AND time_entry_current.person_id =
                               perf_rating_current.person_id(+)
                           AND time_entry_current.person_id (+) =
                               time_entry.person_id))

方法二：使用Oracle专属的UNPIVOT函数（更简洁） #

Oracle 11g及以上版本支持UNPIVOT操作，可以直接将宽表的多列转换为行，写法更简洁：

-- 使用UNPIVOT转换窄表
SELECT SCORE, Quarter
FROM (
    -- 原有的宽表查询语句
    SELECT Greatest(1, Least(5, score))      AS past_score,
           Greatest(1, Least(5, curr_score)) AS current_score,
           current_quarter,
           past_quarter
    FROM   (SELECT Round(( 0.4 * normalized_sick_leave ) +
                         ( 0.3 * normalized_total ) + (
                                0.3 *
                                        overall_rating ), 1) AS score,
                   Round(( 0.4 * normalized_sick_leave_current ) + ( 0.3 *
                         normalized_total_curr )
                         + (
                         0.3 * overall_rating_curr ), 1)     AS curr_score,
                   current_quarter,
                   past_quarter
            FROM   (SELECT Nvl(abs_record.duration / 10, 0)                  AS
                                   normalized_sick_leave,
                           time_entry.hours / 540                            AS
                           normalized_total,
                           5 - To_number(perf_rating.overall_rating)         AS
                           overall_rating,
                           abs_record_current.duration / 10                  AS
                                   normalized_sick_leave_current,
                           time_entry_current.hours / 540                    AS
                                   normalized_total_curr,
                           5 - To_number(perf_rating_current.overall_rating) AS
                           overall_rating_curr
                                   ,
                           time_entry.quarter
                                   past_quarter,
                           time_entry_current.quarter
                           CURRENT_QUARTER
                    FROM   abs_record ABS_RECORD,
                           time_entry TIME_ENTRY,
                           perf_rating perf_rating,
                           abs_record_current ABS_RECORD_current,
                           time_entry_current TIME_ENTRY_current,
                           perf_rating_current perf_rating_current
                    WHERE  abs_record.person_id(+) = time_entry.person_id
                           AND time_entry.person_id = perf_rating.person_id(+)
                           AND abs_record_current.person_id(+) =
                               time_entry_current.person_id
                           AND time_entry_current.person_id =
                               perf_rating_current.person_id(+)
                           AND time_entry_current.person_id (+) =
                               time_entry.person_id))
UNPIVOT (
    (SCORE, Quarter) FOR score_quarter IN (
        (past_score, past_quarter) AS 'past',
        (current_score, current_quarter) AS 'current'
    )
)

说明： #

• UNION ALL的方式兼容性更强，适合所有数据库环境；
• UNPIVOT是Oracle原生支持的列转行函数，代码更简洁，执行效率更高，适合Oracle 11g及以上版本。

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