求助：基于相同列值生成最多4列的Pivot table

原始数据 #

期望输出 #

下面是不同工具下的具体实现方法：

1. SQL 实现 #

先给每个ID下的名称分配序号，再通过Pivot或条件聚合完成转列。

SQL Server 版本

WITH RankedNames AS (
    SELECT 
        ID,
        NAME,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NAME) AS NameRank
    FROM YourTableName -- 替换成你的表名
)
SELECT 
    ID,
    [1] AS NAME1,
    [2] AS NAME2,
    [3] AS NAME3,
    [4] AS NAME4
FROM RankedNames
PIVOT (
    MAX(NAME) FOR NameRank IN ([1], [2], [3], [4])
) AS PivotTable;

MySQL 版本（无原生Pivot）

SELECT 
    ID,
    MAX(CASE WHEN NameRank = 1 THEN NAME END) AS NAME1,
    MAX(CASE WHEN NameRank = 2 THEN NAME END) AS NAME2,
    MAX(CASE WHEN NameRank = 3 THEN NAME END) AS NAME3,
    MAX(CASE WHEN NameRank = 4 THEN NAME END) AS NAME4
FROM (
    SELECT 
        ID,
        NAME,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY NAME) AS NameRank
    FROM YourTableName -- 替换成你的表名
) AS RankedNames
GROUP BY ID;

2. Python Pandas 实现 #

import pandas as pd

# 加载你的数据（这里是示例数据，可替换成读取文件的代码）
df = pd.DataFrame({
    'ID': [1,1,2,2,2,3,3,3],
    'NAME': ['Jim','Joe','Jak','Den','Din','Kos','Kos2','Sah']
})

# 给每个ID下的名称分配1开始的序号
df['NameRank'] = df.groupby('ID').cumcount() + 1

# 透视转列，空值填充为空字符串
pivot_df = df.pivot(index='ID', columns='NameRank', values='NAME').fillna('')

# 重命名列并确保NAME1-NAME4都存在
pivot_df.columns = [f'NAME{col}' for col in pivot_df.columns]
for col in ['NAME1','NAME2','NAME3','NAME4']:
    if col not in pivot_df.columns:
        pivot_df[col] = ''

# 调整列顺序并重置索引
pivot_df = pivot_df[['NAME1','NAME2','NAME3','NAME4']].reset_index()

print(pivot_df)

3. Excel 实现 #

方法1：数据透视表+条件逻辑

1. 选中原始数据，插入数据透视表，将ID拖到「行」区域，NAME拖到「值」区域。
2. 点击值区域的NAME字段，选择「值字段设置」→ 汇总方式选「最大值」（不影响文本显示）。
3. 给原始数据添加辅助列NameRank：在C2单元格输入=COUNTIF($A$2:A2,A2)，下拉填充，得到每个ID下的序号。
4. 用XLOOKUP提取对应序号的名称，比如D2单元格输入=XLOOKUP($A2&1,$A:$A&$C:$C,$B:$B,"")对应NAME1，以此类推。

方法2：Power Query（更高效）

1. 导入数据到Power Query，选择ID列，点击「转换」→「分组依据」，操作选「所有行」，新列名设为Names。
2. 添加自定义列：=Table.AddIndexColumn([Names], "Rank", 1, 1)，给每个分组的名称加序号。
3. 展开自定义列，点击「转换」→「透视列」，行选ID，值选NAME，列选Rank。
4. 重命名列名为NAME1-NAME4，空值替换为空字符串，最后加载回Excel。

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