SQL数据集透视求助：按月份透视、按年份分行实现方案

通用兼容方案（CASE WHEN条件聚合） #

这是所有SQL数据库都支持的写法，核心思路是从period字段提取年份和月份，用CASE WHEN筛选对应月份的指标，再按年份、来源、地区分组聚合。

SELECT
    source,
    region,
    -- 提取年份，不同数据库函数略有差异，见下方说明
    EXTRACT(YEAR FROM period) AS year,
    -- 1月各项指标
    SUM(CASE WHEN EXTRACT(MONTH FROM period) = 1 THEN revenue END) AS jan_revenue,
    AVG(CASE WHEN EXTRACT(MONTH FROM period) = 1 THEN tax_rate END) AS jan_tax_rate,
    SUM(CASE WHEN EXTRACT(MONTH FROM period) = 1 THEN users END) AS jan_users,
    -- 2月各项指标
    SUM(CASE WHEN EXTRACT(MONTH FROM period) = 2 THEN revenue END) AS feb_revenue,
    AVG(CASE WHEN EXTRACT(MONTH FROM period) = 2 THEN tax_rate END) AS feb_tax_rate,
    SUM(CASE WHEN EXTRACT(MONTH FROM period) = 2 THEN users END) AS feb_users,
    -- 请自行补充3-11月的代码，格式与上述一致
    -- 12月各项指标
    SUM(CASE WHEN EXTRACT(MONTH FROM period) = 12 THEN revenue END) AS dec_revenue,
    AVG(CASE WHEN EXTRACT(MONTH FROM period) = 12 THEN tax_rate END) AS dec_tax_rate,
    SUM(CASE WHEN EXTRACT(MONTH FROM period) = 12 THEN users END) AS dec_users
FROM source_data
GROUP BY source, region, EXTRACT(YEAR FROM period)
ORDER BY source, region, year;

数据库适配说明 #

• MySQL/PostgreSQL：可简化为YEAR(period)替代EXTRACT(YEAR FROM period)，MONTH(period)替代EXTRACT(MONTH FROM period)。
• SQL Server/Oracle：同样支持YEAR(period)和MONTH(period)函数。

特定数据库PIVOT语法方案（以SQL Server为例） #

如果你的数据库支持PIVOT语法（如SQL Server、Oracle），可采用这种写法，但注意PIVOT一次仅能处理一个聚合指标，需多次透视后关联：

WITH monthly_data AS (
    SELECT
        source,
        region,
        YEAR(period) AS year,
        MONTH(period) AS month,
        revenue,
        tax_rate,
        users
    FROM source_data
)
SELECT
    source,
    region,
    year,
    [1]_revenue AS jan_revenue,
    [1]_tax_rate AS jan_tax_rate,
    [1]_users AS jan_users,
    [2]_revenue AS feb_revenue,
    [2]_tax_rate AS feb_tax_rate,
    [2]_users AS feb_users,
    -- 自行补充3-11月的字段映射
    [12]_revenue AS dec_revenue,
    [12]_tax_rate AS dec_tax_rate,
    [12]_users AS dec_users
FROM monthly_data
PIVOT (SUM(revenue) FOR month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pvt_rev
JOIN (
    SELECT * FROM monthly_data
    PIVOT (AVG(tax_rate) FOR month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pvt_tax
) ON pvt_rev.source = pvt_tax.source 
    AND pvt_rev.region = pvt_tax.region 
    AND pvt_rev.year = pvt_tax.year
JOIN (
    SELECT * FROM monthly_data
    PIVOT (SUM(users) FOR month IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pvt_users
) ON pvt_rev.source = pvt_users.source 
    AND pvt_rev.region = pvt_users.region 
    AND pvt_rev.year = pvt_users.year
ORDER BY source, region, year;

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