如何在SQL Server中使用Pivot函数实现行转列（最多3条记录）

原表数据 #

期望转置结果 #

实现方案 #

要使用PIVOT完成需求，核心是先给每个id下的记录生成唯一序号，再基于序号分别对account和time字段进行转置，最后关联结果。

步骤1：生成带序号的中间数据集 #

用ROW_NUMBER()函数按id分区、time排序，给每条记录分配序号（1、2、3...），确保每个id下的记录有明确的分组标识：

WITH RankedData AS (
    SELECT 
        id,
        account,
        time,
        rn = ROW_NUMBER() OVER(PARTITION BY id ORDER BY time)
    FROM YourTableName -- 替换为你的实际表名
)

步骤2：使用PIVOT分别转置account和time #

PIVOT只能针对单个聚合字段，因此需要分别对account和time执行转置，再通过id关联两个结果集：

WITH RankedData AS (
    SELECT 
        id,
        account,
        time,
        rn = ROW_NUMBER() OVER(PARTITION BY id ORDER BY time)
    FROM YourTableName
)
SELECT 
    p_id.id,
    p_account.[1] AS account1,
    p_time.[1] AS time1,
    p_account.[2] AS account2,
    p_time.[2] AS time2,
    p_account.[3] AS account3,
    p_time.[3] AS time3
FROM (SELECT DISTINCT id FROM RankedData) AS p_id
LEFT JOIN (
    SELECT id, [1], [2], [3]
    FROM (SELECT id, rn, account FROM RankedData) AS src
    PIVOT (MAX(account) FOR rn IN ([1], [2], [3])) AS pvt
) AS p_account ON p_id.id = p_account.id
LEFT JOIN (
    SELECT id, [1], [2], [3]
    FROM (SELECT id, rn, time FROM RankedData) AS src
    PIVOT (MAX(time) FOR rn IN ([1], [2], [3])) AS pvt
) AS p_time ON p_id.id = p_time.id

说明 #

• 用LEFT JOIN替代内连接，确保即使id下没有3条记录，也能保留该id的结果，缺失的字段会显示NULL（与期望结果的空值一致）。
• MAX()聚合函数在这里仅作为占位，因为每个rn在同一id下唯一，聚合后仍为原字段值。
• 如果需要调整排序逻辑，修改ROW_NUMBER()中的ORDER BY子句即可（比如按account排序）。

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