如何将Oracle分组查询结果按年份转为动态列并合并为单行

需求说明 #

需要将T_STORAGE表中的数据按产品分组，把不同年份的StorageCount和Total转换为动态列，列名随表中实际存在的年份自动调整。

静态列实现（年份范围固定时） #

如果已知所有需要展示的年份，可以用CASE WHEN结合聚合函数快速实现：

SELECT
    ProductName,
    SUM(CASE WHEN EXTRACT(YEAR FROM StorageTime) = 2021 THEN StorageCount ELSE 0 END) AS "2021 StorageCount",
    SUM(CASE WHEN EXTRACT(YEAR FROM StorageTime) = 2021 THEN Total ELSE 0 END) AS "2021 Total",
    SUM(CASE WHEN EXTRACT(YEAR FROM StorageTime) = 2022 THEN StorageCount ELSE 0 END) AS "2022 StorageCount",
    SUM(CASE WHEN EXTRACT(YEAR FROM StorageTime) = 2022 THEN Total ELSE 0 END) AS "2022 Total",
    SUM(CASE WHEN EXTRACT(YEAR FROM StorageTime) = 2023 THEN StorageCount ELSE 0 END) AS "2023 StorageCount",
    SUM(CASE WHEN EXTRACT(YEAR FROM StorageTime) = 2023 THEN Total ELSE 0 END) AS "2023 Total"
FROM T_STORAGE
GROUP BY ProductName;

也可以使用Oracle原生的PIVOT语法：

SELECT *
FROM (
    SELECT
        ProductName,
        EXTRACT(YEAR FROM StorageTime) AS StorageYear,
        StorageCount,
        Total
    FROM T_STORAGE
)
PIVOT (
    SUM(StorageCount) AS StorageCount,
    SUM(Total) AS Total
    FOR StorageYear IN (2021, 2022, 2023)
)
ORDER BY ProductName;

动态列实现（年份自动适配） #

因为年份是动态变化的，必须通过动态SQL自动生成列定义，以下是两种可行方案：

方案1：基于CASE WHEN的动态SQL #

DECLARE
    v_sql VARCHAR2(4000);
    v_year_cols VARCHAR2(2000);
BEGIN
    -- 拼接所有年份对应的列片段
    SELECT LISTAGG(
        'SUM(CASE WHEN EXTRACT(YEAR FROM StorageTime) = ' || StorageYear || ' THEN StorageCount ELSE 0 END) AS "' || StorageYear || ' StorageCount",' ||
        'SUM(CASE WHEN EXTRACT(YEAR FROM StorageTime) = ' || StorageYear || ' THEN Total ELSE 0 END) AS "' || StorageYear || ' Total"',
        ','
    ) WITHIN GROUP (ORDER BY StorageYear)
    INTO v_year_cols
    FROM (SELECT DISTINCT EXTRACT(YEAR FROM StorageTime) AS StorageYear FROM T_STORAGE);

    -- 拼接完整执行SQL
    v_sql := 'SELECT ProductName, ' || v_year_cols || ' FROM T_STORAGE GROUP BY ProductName ORDER BY ProductName';

    -- 执行动态SQL
    EXECUTE IMMEDIATE v_sql;
END;
/

方案2：基于PIVOT的动态SQL #

DECLARE
    v_sql VARCHAR2(4000);
    v_year_list VARCHAR2(1000);
    v_pivot_cols VARCHAR2(2000);
BEGIN
    -- 生成去重后的年份列表（格式：2021,2022,2023）
    SELECT LISTAGG(StorageYear, ',') WITHIN GROUP (ORDER BY StorageYear)
    INTO v_year_list
    FROM (SELECT DISTINCT EXTRACT(YEAR FROM StorageTime) AS StorageYear FROM T_STORAGE);

    -- 生成PIVOT结果的列别名映射
    SELECT LISTAGG(
        StorageYear || ' AS "' || StorageYear || ' StorageCount",' ||
        StorageYear || '_Total AS "' || StorageYear || ' Total"',
        ','
    ) WITHIN GROUP (ORDER BY StorageYear)
    INTO v_pivot_cols
    FROM (SELECT DISTINCT EXTRACT(YEAR FROM StorageTime) AS StorageYear FROM T_STORAGE);

    -- 拼接完整执行SQL
    v_sql := 'SELECT ProductName, ' || v_pivot_cols || ' FROM (
        SELECT ProductName, EXTRACT(YEAR FROM StorageTime) AS StorageYear, StorageCount, Total FROM T_STORAGE
    ) PIVOT (
        SUM(StorageCount) AS StorageCount, SUM(Total) AS Total
        FOR StorageYear IN (' || v_year_list || ')
    ) ORDER BY ProductName';

    -- 执行动态SQL
    EXECUTE IMMEDIATE v_sql;
END;
/

补充说明 #

• 动态SQL会自动扫描表中所有存在的年份，生成对应的列，无需手动维护年份列表。
• 如果需要将结果返回给客户端，可以在存储过程中定义SYS_REFCURSOR类型的输出参数，将动态SQL的执行结果存入游标返回。

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