Oracle SQL实现列转行：客户账户年度月度报表生成需求

现有表结构与样本数据 #

我们有一张名为customers的表，存储客户账户明细，字段包括Month、accounts、inactive_accs，样本数据如下：

报表生成需求 #

需通过Oracle SQL将列数据转换为行数据，生成满足以下要求的报表：

• 首列「2023」需包含2023年accounts与inactive_accs的总计值；
• inactive_accs需按月度累加至年末（例如：Jan-24+Feb-24+Mar-24=Mar-24对应值）；
• 末列「2024」需包含截至对应月/年末的accounts与inactive_accs总计值；
• 支持新增月份时自动扩展报表列并更新对应计算值，新增月份后的报表示例如下：

解决方案 #

步骤1：数据预处理（计算累计值与年度总计） #

先通过CTE整理数据，计算2024年inactive_accs的累计值，以及各年度的总计：

WITH data_prep AS (
    SELECT
        'Accounts' AS metric_type,
        Month,
        accounts AS value,
        -- 2024年度累计账户数（截至当前月）
        SUM(accounts) OVER (PARTITION BY EXTRACT(YEAR FROM Month) ORDER BY Month) AS ytd_val,
        -- 2023年度总计
        SUM(accounts) OVER (PARTITION BY EXTRACT(YEAR FROM Month)) AS year_total
    FROM customers
    UNION ALL
    SELECT
        'inactive_accs' AS metric_type,
        Month,
        inactive_accs AS value,
        -- 2024年度累计 inactive 数（截至当前月）
        SUM(inactive_accs) OVER (PARTITION BY EXTRACT(YEAR FROM Month) ORDER BY Month) AS ytd_val,
        -- 2023年度总计
        SUM(inactive_accs) OVER (PARTITION BY EXTRACT(YEAR FROM Month)) AS year_total
    FROM customers
)
SELECT * FROM data_prep;

步骤2：动态SQL实现自动列扩展 #

由于需要自动适配新增月份，必须使用动态SQL生成PIVOT语句：

DECLARE
    v_cols VARCHAR2(4000);
    v_sql VARCHAR2(4000);
BEGIN
    -- 生成动态列列表：2023、所有2024年月度列、2024
    SELECT LISTAGG(
        CASE
            WHEN col_name = '2023' THEN '''2023'' AS "2023"'
            WHEN col_name = '2024' THEN '''2024'' AS "2024"'
            ELSE '''' || col_name || ''' AS "' || col_name || '"'
        END, ', '
    ) WITHIN GROUP (ORDER BY
        CASE col_name
            WHEN '2023' THEN 1
            WHEN '2024' THEN 999
            ELSE TO_DATE(col_name, 'Mon-YY')
        END
    ) INTO v_cols
    FROM (
        SELECT DISTINCT
            CASE
                WHEN EXTRACT(YEAR FROM Month) = 2023 THEN '2023'
                ELSE TO_CHAR(Month, 'Mon-YY')
            END AS col_name
        FROM customers
        UNION ALL
        SELECT '2024' FROM dual
    );

    -- 构建完整动态SQL
    v_sql := '
        WITH data_prep AS (
            SELECT
                ''Accounts'' AS metric_type,
                Month,
                accounts AS value,
                SUM(accounts) OVER (PARTITION BY EXTRACT(YEAR FROM Month) ORDER BY Month) AS ytd_val,
                SUM(accounts) OVER (PARTITION BY EXTRACT(YEAR FROM Month)) AS year_total
            FROM customers
            UNION ALL
            SELECT
                ''inactive_accs'' AS metric_type,
                Month,
                inactive_accs AS value,
                SUM(inactive_accs) OVER (PARTITION BY EXTRACT(YEAR FROM Month) ORDER BY Month) AS ytd_val,
                SUM(inactive_accs) OVER (PARTITION BY EXTRACT(YEAR FROM Month)) AS year_total
            FROM customers
        )
        SELECT
            metric_type AS "",
            "2023",
            ' || REPLACE(v_cols, '''2023'' AS "2023", ', '') || '
        FROM (
            SELECT
                metric_type,
                col_name,
                CASE
                    WHEN col_name = ''2023'' THEN year_total
                    WHEN col_name = ''2024'' THEN ytd_val
                    ELSE value
                END AS metric_value
            FROM data_prep
            CROSS JOIN (
                SELECT DISTINCT
                    CASE
                        WHEN EXTRACT(YEAR FROM Month) = 2023 THEN ''2023''
                        ELSE TO_CHAR(Month, ''Mon-YY'')
                    END AS col_name
                FROM customers
                UNION ALL
                SELECT ''2024'' FROM dual
            ) cols
            WHERE
                (col_name = ''2023'' AND EXTRACT(YEAR FROM Month) = 2023)
                OR (col_name = TO_CHAR(Month, ''Mon-YY''))
                OR (col_name = ''2024'' AND EXTRACT(YEAR FROM Month) = 2024)
        )
        PIVOT (
            MAX(metric_value)
            FOR col_name IN (' || v_cols || ')
        )
        ORDER BY
            CASE metric_type
                WHEN ''Accounts'' THEN 1
                WHEN ''inactive_accs'' THEN 2
            END;
    ';

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

关键说明 #

• 动态SQL会自动从customers表中提取所有月份，生成对应的报表列；
• 2023列直接取当年accounts和inactive_accs的总计；
• 2024各月份列中，Accounts显示当月数值，inactive_accs显示截至当月的累计值；
• 2024列显示截至对应月份的累计总计（与该月的累计值一致）；
• 当新增月份数据（如2024-05-01）后，重新执行该动态SQL即可自动扩展列并更新计算值。

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