如何将SQL中分类行转置为列并填充NA值？

1. SQL 实现 #

通用写法（适配多数数据库） #

通过GROUP BY结合CASE WHEN与COALESCE函数，将Test列的分类转为独立列，并为缺失记录填充na：

SELECT 
    Student,
    COALESCE(MAX(CASE WHEN Test = 'T1' THEN Grade END), 'na') AS T1,
    COALESCE(MAX(CASE WHEN Test = 'T2' THEN Grade END), 'na') AS T2,
    COALESCE(MAX(CASE WHEN Test = 'T3' THEN Grade END), 'na') AS T3
FROM 
    your_table_name  -- 替换为你的实际表名
GROUP BY 
    Student;

PostgreSQL 专用写法（使用crosstab函数） #

PostgreSQL支持crosstab函数实现快速转置，需先启用tablefunc扩展：

-- 启用扩展（仅需执行一次）
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- 转置并填充缺失值
SELECT 
    Student,
    COALESCE(T1, 'na') AS T1,
    COALESCE(T2, 'na') AS T2,
    COALESCE(T3, 'na') AS T3
FROM crosstab(
    'SELECT Student, Test, Grade FROM your_table_name ORDER BY 1,2',
    'SELECT unnest(ARRAY[''T1'', ''T2'', ''T3''])'
) AS ct(Student text, T1 text, T2 text, T3 text);

2. Python Pandas 实现 #

利用pivot_table完成转置，再通过fillna填充缺失值：

import pandas as pd

# 读取你的数据（示例为构造数据，实际可通过pd.read_sql/pd.read_csv读取）
data = {
    'Student': ['St1', 'St1', 'St2', 'St2', 'St3'],
    'Test': ['T1', 'T2', 'T2', 'T3', 'T3'],
    'Grade': ['A', 'B', 'B', 'C', 'B']
}
df = pd.DataFrame(data)

# 转置操作
pivoted_df = df.pivot_table(
    index='Student',
    columns='Test',
    values='Grade',
    aggfunc='first'  # 确保每个学生对应每个考试仅保留一个成绩
).reset_index()

# 清理列名并填充缺失值
pivoted_df.columns.name = None
pivoted_df = pivoted_df.fillna('na')

# 输出结果
print(pivoted_df)

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