如何对具有不同数值数量的行执行PIVOT（行转列）操作？

以下是针对不同工具的具体实现方案，把原始的TITLE对应多个CODE的多行数据，转换为每个TITLE一行、对应多列修订值的格式：

1. SQL 实现 #

假设原始数据存储在名为revision_data的表中，表结构和数据如下：

CREATE TABLE revision_data (
    TITLE VARCHAR(50),
    CODE VARCHAR(10)
);

INSERT INTO revision_data VALUES
('ABCDE-AA-XXX-PD-001', 'A'),
('ABCDE-AA-XXX-PD-001', 'B'),
('ABCDE-AA-XXX-PD-001', 'C'),
('ABCDE-AA-XXX-PD-001', 'D'),
('GBSRH-AF-DXY-PD-001', '1'),
('GBSRH-AF-DXY-PD-001', '2'),
('GBSRH-AF-DXY-PD-001', '3'),
('GBSRH-AF-DXY-PD-001', '4'),
('GBSRH-AF-DXY-PD-001', '5'),
('GBSRH-AF-DXY-PD-001', '6');

SQL Server 语法 #

使用原生PIVOT函数，先给每个分组内的CODE生成序号：

WITH ranked_data AS (
    SELECT 
        TITLE,
        CODE,
        'REV_' + CAST(ROW_NUMBER() OVER (PARTITION BY TITLE ORDER BY CODE) AS VARCHAR) AS rev_col
    FROM revision_data
)
SELECT *
FROM ranked_data
PIVOT (
    MAX(CODE) FOR rev_col IN ([REV_1], [REV_2], [REV_3], [REV_4], [REV_5], [REV_6])
) AS pivoted;

MySQL 语法 #

MySQL无原生PIVOT，用分组+条件聚合实现：

SELECT
    TITLE,
    MAX(CASE WHEN rn = 1 THEN CODE END) AS REV_1,
    MAX(CASE WHEN rn = 2 THEN CODE END) AS REV_2,
    MAX(CASE WHEN rn = 3 THEN CODE END) AS REV_3,
    MAX(CASE WHEN rn = 4 THEN CODE END) AS REV_4,
    MAX(CASE WHEN rn = 5 THEN CODE END) AS REV_5,
    MAX(CASE WHEN rn = 6 THEN CODE END) AS REV_6
FROM (
    SELECT 
        TITLE,
        CODE,
        ROW_NUMBER() OVER (PARTITION BY TITLE ORDER BY CODE) AS rn
    FROM revision_data
) AS ranked
GROUP BY TITLE;

PostgreSQL 语法 #

使用crosstab函数，需先启用tablefunc扩展：

-- 启用扩展
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
    'SELECT TITLE, rn, CODE FROM (
        SELECT 
            TITLE,
            CODE,
            ROW_NUMBER() OVER (PARTITION BY TITLE ORDER BY CODE) AS rn
        FROM revision_data
    ) AS ranked ORDER BY 1,2',
    'SELECT generate_series(1,6)'
) AS ct(
    TITLE VARCHAR(50),
    REV_1 VARCHAR(10),
    REV_2 VARCHAR(10),
    REV_3 VARCHAR(10),
    REV_4 VARCHAR(10),
    REV_5 VARCHAR(10),
    REV_6 VARCHAR(10)
);

2. Python Pandas 实现 #

通过分组生成序号后，用pivot完成行转列：

import pandas as pd

# 构造原始数据
data = [
    ['ABCDE-AA-XXX-PD-001', 'A'],
    ['ABCDE-AA-XXX-PD-001', 'B'],
    ['ABCDE-AA-XXX-PD-001', 'C'],
    ['ABCDE-AA-XXX-PD-001', 'D'],
    ['GBSRH-AF-DXY-PD-001', '1'],
    ['GBSRH-AF-DXY-PD-001', '2'],
    ['GBSRH-AF-DXY-PD-001', '3'],
    ['GBSRH-AF-DXY-PD-001', '4'],
    ['GBSRH-AF-DXY-PD-001', '5'],
    ['GBSRH-AF-DXY-PD-001', '6']
]

df = pd.DataFrame(data, columns=['TITLE', 'CODE'])

# 给每个TITLE内的CODE添加序号
df['REV_NUM'] = df.groupby('TITLE').cumcount() + 1
# 行转列并重命名列
pivoted_df = df.pivot(index='TITLE', columns='REV_NUM', values='CODE').rename(columns=lambda x: f'REV_{x}')
# 重置索引为普通列
pivoted_df = pivoted_df.reset_index()

print(pivoted_df)

3. Excel 实现 #

方法1：数据透视表 #

1. 将原始数据粘贴到Excel（A列TITLE，B列CODE）。
2. 选中数据区域，点击插入→数据透视表，选择放置位置。
3. 把TITLE拖到行区域，CODE拖到值区域。
4. 点击值区域的CODE→值字段设置，选择最大值（因分组内CODE唯一，聚合方式不影响结果）。
5. 点击行标签的TITLE→字段设置→布局和打印，勾选以表格形式显示，最后调整列名即可。

方法2：数组公式 #

在C2单元格输入以下公式，按Ctrl+Shift+Enter确认（数组公式），然后向右、向下填充：

=IFERROR(INDEX($B:$B, SMALL(IF($A:$A=$A2, ROW($B:$B)-ROW($B$1)+1), COLUMN(A:A))), "")

内容的提问来源于stack exchange，提问作者Leonardo Vicente Rapirap