使用Pivot实现员工薪资数据表的多列数据透视查询需求

嘿，我来帮你搞定这个数据透视的需求！针对你给出的原始数据和期望的结果格式，我准备了两种常用的实现方案——SQL（以SQL Server为例）和Python Pandas，都能完美实现将ProcessingMonth转换为12个月份列、缺失月份自动填充0的效果。

先明确原始数据与目标 #

原始数据表（整理后） #

期望目标 #

将月份转为列，缺失月份填充0，最终结构如下：

Employee_ID year jan feb mar apr may june jul aug sep oct nov dec
3           2017 0   0   41668.00 41668.00 41668.00 41668.00 0 0 0 0 0 0
4           2017 0   0   10037.92 10037.92 10037.92 10037.92 0 0 0 0 0 0

方案1：SQL（SQL Server）实现 #

用PIVOT配合CTE生成完整月份列表，确保所有月份都能展示，缺失值自动填0：

-- 生成12个月份的基础映射表
WITH Months AS (
    SELECT 1 AS MonthNum, 'jan' AS MonthName
    UNION SELECT 2, 'feb'
    UNION SELECT 3, 'mar'
    UNION SELECT 4, 'apr'
    UNION SELECT 5, 'may'
    UNION SELECT 6, 'june'
    UNION SELECT 7, 'jul'
    UNION SELECT 8, 'aug'
    UNION SELECT 9, 'sep'
    UNION SELECT 10, 'oct'
    UNION SELECT 11, 'nov'
    UNION SELECT 12, 'dec'
),
-- 关联员工-年份组合与完整月份，确保每个员工每年都有12条记录
EmployeeMonthData AS (
    SELECT 
        emp.Employee_ID,
        emp.FinancialYear AS year,
        m.MonthName,
        -- 缺失月份填充0
        ISNULL(original.Amount, 0) AS Amount
    FROM Months m
    -- 先获取所有唯一的员工+年份组合
    CROSS JOIN (SELECT DISTINCT Employee_ID, FinancialYear FROM YourTableName) emp
    -- 左连接原始数据，匹配对应月份
    LEFT JOIN YourTableName original 
        ON emp.Employee_ID = original.Employee_ID
        AND emp.FinancialYear = original.FinancialYear
        AND MONTH(DATEFROMPARTS(2000, CASE original.ProcessingMonth
            WHEN 'March' THEN 3 WHEN 'April' THEN 4 WHEN 'May' THEN 5 WHEN 'June' THEN 6
            WHEN 'January' THEN 1 WHEN 'February' THEN 2 WHEN 'July' THEN 7 WHEN 'August' THEN 8
            WHEN 'September' THEN 9 WHEN 'October' THEN 10 WHEN 'November' THEN 11 WHEN 'December' THEN 12
        END, 1)) = m.MonthNum
)
-- 执行透视，将行转列
SELECT 
    Employee_ID,
    year,
    jan, feb, mar, apr, may, june, jul, aug, sep, oct, nov, dec
FROM EmployeeMonthData
PIVOT (
    SUM(Amount)
    FOR MonthName IN (jan, feb, mar, apr, may, june, jul, aug, sep, oct, nov, dec)
) AS PivotResult
ORDER BY Employee_ID;

关键说明： #

• 用CTE生成12个月份的完整列表，保证不会漏掉任何一个月份
• CROSS JOIN获取所有员工+年份的组合，再和原始数据左连接，缺失月份自动填充0
• 通过CASE语句将月份名称转为数字，确保和基础月份表匹配

方案2：Python Pandas实现 #

用pivot_table配合reindex快速实现需求，代码简洁易读：

import pandas as pd

# 构建原始数据（如果是从文件读取，替换成pd.read_csv等即可）
data = {
    'Employee_ID': [3,3,3,3,4,4,4,4],
    'ProcessingMonth': ['April','June','March','May','April','June','March','May'],
    'Amount': [41668.00,41668.00,41668.00,41668.00,10037.92,10037.92,10037.92,10037.92],
    'FinancialYear': [2017]*8
}

df = pd.DataFrame(data)

# 定义目标月份顺序和名称映射
month_order = ['jan','feb','mar','apr','may','june','jul','aug','sep','oct','nov','dec']
month_name_map = {
    'March':'mar', 'April':'apr', 'May':'may', 'June':'june',
    'January':'jan', 'February':'feb', 'July':'jul', 'August':'aug',
    'September':'sep', 'October':'oct', 'November':'nov', 'December':'dec'
}

# 转换原始月份名称为目标缩写
df['Month'] = df['ProcessingMonth'].map(month_name_map)

# 生成透视表，缺失值直接填0
pivot_result = df.pivot_table(
    index=['Employee_ID', 'FinancialYear'],
    columns='Month',
    values='Amount',
    aggfunc='sum',
    fill_value=0
).reindex(columns=month_order).reset_index()

# 重命名列名匹配目标格式
pivot_result.rename(columns={'FinancialYear':'year'}, inplace=True)

# 打印结果
print(pivot_result.to_string(index=False))

运行结果： #

Employee_ID  year  jan  feb      mar      apr      may    june  jul  aug  sep  oct  nov  dec
           3  2017    0    0  41668.0  41668.0  41668.0  41668.0    0    0    0    0    0    0
           4  2017    0    0  10037.92 10037.92 10037.92 10037.92    0    0    0    0    0    0

关键说明： #

• 用month_name_map转换月份名称为目标缩写，保证列名匹配
• pivot_table的fill_value=0直接填充缺失月份的值
• reindex确保12个月份按指定顺序显示，不会乱序或缺失

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