如何在PL/SQL中实现数据行转列？附输入输出示例

需求说明 #

把车辆成本数据从行格式转成列格式，按车辆编号和车辆名称聚合，每个“月份_年份”作为单独列展示对应成本。

输入数据 #

车辆编号 车辆名称 月份 年份 成本
1111     Car      Aug  2022 $5000
1111     Car      Sep  2022 $5100
1111     Car      Oct  2022 $5300
1111     Car      Nov  2022 $5000
1111     Car      Dec  2022 $5000
1111     Car      Jan  2023 $5000
1111     Car      Feb  2023 $5000
...
1111     Car      Aug  2023 $10000
1111     Car      Sep  2023 $10000
1111     Car      Oct  2023 $10000
1111     Car      Nov  2023 $10000
1111     Car      Dec  2023 $10000
2222     Truck    Aug  2022 $10000
2222     Truck    Sep  2022 $10000
2222     Truck    Oct  2022 $10000
2222     Truck    Nov  2022 $10000
2222     Truck    Dec  2022 $10000
2222     Truck    Jan  2023 $10000
2222     Truck    Feb  2023 $10000
...
2222     Truck    Dec  2023 $5000

预期输出 #

车辆编号 车辆名称 Aug_2022 Sep_2022 Oct_2022 ... Dec_2023
1111     Car     $5000    $5100    $5300    ... $10000
2222     Truck   $10000   $10000   $10000   ... $5000

实现方案 #

1. 静态列转换（已知所有年月列）

如果能确定需要转换的所有“月份_年份”列，直接用PIVOT函数即可，写法简单：

SELECT *
FROM (
    SELECT 
        Vechical_NUM AS 车辆编号,
        Vehical_name AS 车辆名称,
        Period || '_' || Year AS 年月,
        cost AS 成本
    FROM 你的表名 -- 替换成实际表名
)
PIVOT (
    MAX(成本) FOR 年月 IN (
        'Aug_2022' AS Aug_2022,
        'Sep_2022' AS Sep_2022,
        'Oct_2022' AS Oct_2022,
        -- 按需求添加所有需要的年月列
        'Dec_2023' AS Dec_2023
    )
);

2. 动态列转换（年月列不确定）

如果后续会新增年月数据，手动维护列太麻烦，可以用PL/SQL动态生成SQL：

CREATE OR REPLACE PROCEDURE PIVOT_VEHICLE_COST IS
    v_sql    VARCHAR2(4000);
    v_cols   VARCHAR2(4000);
BEGIN
    -- 自动获取所有唯一的年月并生成列定义
    SELECT LISTAGG('''' || Period || '_' || Year || ''' AS ' || Period || '_' || Year, ', ')
        INTO v_cols
    FROM (
        SELECT DISTINCT Period, Year 
        FROM 你的表名 -- 替换成实际表名
        ORDER BY Year, TO_CHAR(TO_DATE(Period, 'Mon'), 'MM') -- 按年月排序
    );
    
    -- 拼接完整的PIVOT查询SQL
    v_sql := '
        SELECT *
        FROM (
            SELECT 
                Vechical_NUM AS 车辆编号,
                Vehical_name AS 车辆名称,
                Period || ''_'' || Year AS 年月,
                cost AS 成本
            FROM 你的表名
        )
        PIVOT (
            MAX(成本) FOR 年月 IN (' || v_cols || ')
        )';
    
    -- 执行动态SQL
    EXECUTE IMMEDIATE v_sql;
END;
/

调用这个存储过程就能自动生成包含所有年月列的结果集。

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