如何在PostgreSQL中实现列转置（Pivot）操作？

方法1：SQL实现 #

假设你的原始表名为care_teams，且数据对应要生成4列的行数（比如2行），可以通过窗口函数标记行号，再用条件聚合完成转置：

SELECT
  MAX(CASE WHEN row_num = 1 THEN care_team END) AS care_team_1,
  MAX(CASE WHEN row_num = 1 THEN city END) AS city_1,
  MAX(CASE WHEN row_num = 2 THEN care_team END) AS care_team_2,
  MAX(CASE WHEN row_num = 2 THEN city END) AS city_2
FROM (
  SELECT
    care_team,
    city,
    ROW_NUMBER() OVER (ORDER BY care_team) AS row_num
  FROM care_teams
) t;

如果数据行数更多，只需按规律新增对应的CASE语句即可。

方法2：Python Pandas实现 #

用Pandas将长表转为宽表，直接得到单行多列的结果：

import pandas as pd

# 示例原始数据（替换为你的实际数据）
df = pd.DataFrame({
    'care_team': ['Team A', 'Team B'],
    'city': ['New York', 'London']
})

# 添加行号并转置
df['row_idx'] = df.index + 1
wide_df = df.pivot(columns='row_idx', values=['care_team', 'city'])

# 整理列名并转为单行
wide_df.columns = [f'{col[0]}_{col[1]}' for col in wide_df.columns]
result = wide_df.reset_index(drop=True)

print(result)

运行后会生成包含care_team_1、city_1、care_team_2、city_2列的单行数据表。

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