如何通过SQL基于双条件透视表并实现指定数据结构转换？

核心思路是先按ID+Date作为分组依据，给每组内的Designation生成连续序号，再基于这个序号做透视，就能避免常规透视产生多余列。

起始表（Table 1） #

目标结果表（Table 2） #

1. Excel 操作步骤 #

• 添加辅助列（比如D列），在D2单元格输入公式：
  =COUNTIFS($A$2:A2,A2,$B$2:B2,B2)
  下拉填充到所有行，这会给每个ID+Date组内的记录生成1、2、3...的序号
• 选中整个数据区域，插入数据透视表：
  • 行区域：添加ID和Date
  • 列区域：添加辅助列
  • 值区域：添加Designation（值字段设置选“最大值”或“最小值”，因为每组内每个序号对应唯一值）
• 最后把透视表的列名改成Designation1、Designation2等，空值留空即可

2. SQL 实现（以MySQL为例） #

用窗口函数生成组内序号，再通过条件聚合实现透视：

SELECT 
  ID,
  Date,
  MAX(CASE WHEN rn = 1 THEN Designation END) AS Designation1,
  MAX(CASE WHEN rn = 2 THEN Designation END) AS Designation2,
  MAX(CASE WHEN rn = 3 THEN Designation END) AS Designation3
FROM (
  SELECT 
    ID, Date, Designation,
    -- 按ID+Date分组，给Designation排序生成序号
    ROW_NUMBER() OVER(PARTITION BY ID, Date ORDER BY Designation) AS rn
  FROM your_table
) t
GROUP BY ID, Date;

如果是SQL Server，也可以用PIVOT语法，但条件聚合的兼容性更强。

3. Python Pandas 实现 #

通过分组生成序号后透视：

import pandas as pd

# 读取或构造原始数据
df = pd.DataFrame({
    'ID': [1,1,1,2,2,2,3,4],
    'Date': ['12/6','12/6','12/6','12/6','12/6','12/3','1/2','2/4'],
    'Designation': ['a','b','c','d','e','f','g','h']
})

# 生成组内连续序号（从1开始）
df['rn'] = df.groupby(['ID', 'Date']).cumcount() + 1

# 透视并整理列名
pivoted_df = df.pivot_table(
    index=['ID', 'Date'],
    columns='rn',
    values='Designation',
    aggfunc='first'
).reset_index()

# 重命名列
pivoted_df.columns = ['ID', 'Date'] + [f'Designation{col}' for col in pivoted_df.columns if col not in ['ID', 'Date']]

# 空值填充为空字符串
pivoted_df = pivoted_df.fillna('')

print(pivoted_df)

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