SQL Server中Pivoting行转列：多行转单行列展示问题咨询

我懂你为啥常规PIVOT搞不定了——普通透视一般是针对单个字段的聚合转列，而你要把同一ReferenceID下的多条记录，按顺序拆成带序号的多组字段（Field1_1、Field2_1，Field1_2、Field2_2...），这得先给每行加个序号，再用条件聚合来实现，下面给你两种实用方案：

方法1：手动指定列数（适合固定行数的场景） #

如果你的数据里每个ReferenceID最多只有3行，直接用窗口函数ROW_NUMBER()给行编号，再通过条件聚合转列就行：

WITH NumberedRows AS (
    SELECT 
        ReferenceID,
        Field1,
        Field2,
        Field3,
        -- 按你需要的排序逻辑调整ORDER BY，这里用SELECT NULL只是占位
        ROW_NUMBER() OVER (PARTITION BY ReferenceID ORDER BY (SELECT NULL)) AS RowNum
    FROM YourTableName
)
SELECT
    ReferenceID,
    MAX(CASE WHEN RowNum = 1 THEN Field1 END) AS Field1_1,
    MAX(CASE WHEN RowNum = 1 THEN Field2 END) AS Field2_1,
    MAX(CASE WHEN RowNum = 1 THEN Field3 END) AS Field3_1,
    MAX(CASE WHEN RowNum = 2 THEN Field1 END) AS Field1_2,
    MAX(CASE WHEN RowNum = 2 THEN Field2 END) AS Field2_2,
    MAX(CASE WHEN RowNum = 2 THEN Field3 END) AS Field3_2,
    MAX(CASE WHEN RowNum = 3 THEN Field1 END) AS Field1_3,
    MAX(CASE WHEN RowNum = 3 THEN Field2 END) AS Field2_3,
    MAX(CASE WHEN RowNum = 3 THEN Field3 END) AS Field3_3
FROM NumberedRows
GROUP BY ReferenceID;

要是你有特定的行顺序要求（比如按Field1排序），把ORDER BY (SELECT NULL)换成实际的列就行。

方法2：动态SQL（适配任意行数的场景） #

如果每个ReferenceID的行数不固定，手动写列太麻烦，用动态SQL自动生成所有需要的列：

DECLARE @Columns NVARCHAR(MAX), @SQL NVARCHAR(MAX);

-- 先算出每个ReferenceID最多有多少行
WITH MaxRows AS (
    SELECT MAX(RowNum) AS MaxRowCount
    FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY ReferenceID ORDER BY (SELECT NULL)) AS RowNum
        FROM YourTableName
    ) t
),
-- 生成1到最大行数的序列（兼容SQL Server全版本）
RowNums AS (
    SELECT TOP (SELECT MaxRowCount FROM MaxRows) 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
    FROM sys.all_columns
)
-- 拼接所有需要的列语句
SELECT @Columns = STRING_AGG(
    CONCAT(
        'MAX(CASE WHEN RowNum = ', RowNum, ' THEN Field1 END) AS Field1_', RowNum, ',',
        'MAX(CASE WHEN RowNum = ', RowNum, ' THEN Field2 END) AS Field2_', RowNum, ',',
        'MAX(CASE WHEN RowNum = ', RowNum, ' THEN Field3 END) AS Field3_', RowNum
    ),
    ','
)
FROM RowNums;

-- 组装完整的SQL并执行
SET @SQL = CONCAT('
WITH NumberedRows AS (
    SELECT 
        ReferenceID,
        Field1,
        Field2,
        Field3,
        ROW_NUMBER() OVER (PARTITION BY ReferenceID ORDER BY (SELECT NULL)) AS RowNum
    FROM YourTableName
)
SELECT ReferenceID, ', @Columns, '
FROM NumberedRows
GROUP BY ReferenceID;
');

EXEC sp_executesql @SQL;

要是你用的是SQL Server 2022及以上版本，也可以把生成行号序列的部分换成GENERATE_SERIES(1, (SELECT MaxRowCount FROM MaxRows))，写法会更简洁。

为啥常规PIVOT不行？ #

普通PIVOT只能针对单个字段做行转列，而你需要同时把Field1、Field2、Field3三个字段按行号拆分，常规PIVOT没法一次性处理多字段的转列需求，所以条件聚合才是更合适的选择。

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