SQL Server中实现Pivot转换并生成唯一GENERATE-ID列求助

1. 先明确测试数据结构（假设你的源表结构如下） #

CREATE TABLE TestData (
    Location VARCHAR(10),
    Result VARCHAR(10),
    RecordTime DATETIME
);

INSERT INTO TestData VALUES
('A002', 'Fail', '2024-01-01 10:00:00'),
('A002', 'Pass', '2024-01-01 10:30:00'),
('A002', 'Fail', '2024-01-02 09:00:00'),
('A002', 'Pass', '2024-01-02 09:15:00'),
('B001', 'Fail', '2024-01-01 14:00:00'),
('B001', 'Pass', '2024-01-01 14:20:00');

2. 核心解决思路 #

直接Pivot会把同一Location的所有结果合并成一行，根源是缺少成对记录的分组标记。解决步骤分为两步：先给每一对Fail/Pass分配唯一组ID，再基于组ID进行Pivot转换，同时生成全局唯一标识。

步骤1：为成对记录分配组编号 #

使用ROW_NUMBER()窗口函数，按Location分区、Result分组，再按记录时间排序，为同一Location下的第n个Fail和第n个Pass分配相同的组ID：

WITH RankedData AS (
    SELECT 
        Location,
        Result,
        RecordTime,
        ROW_NUMBER() OVER (PARTITION BY Location, Result ORDER BY RecordTime) AS PairGroup
    FROM TestData
)

步骤2：执行Pivot转换并生成唯一ID #

基于分组后的数据集，用PIVOT转置提取失败/通过时间，同时用NEWID()生成全局唯一的GENERATE-ID（也可根据业务规则生成自定义唯一ID）：

WITH RankedData AS (
    SELECT 
        Location,
        Result,
        RecordTime,
        ROW_NUMBER() OVER (PARTITION BY Location, Result ORDER BY RecordTime) AS PairGroup
    FROM TestData
)
SELECT
    -- 生成GUID类型全局唯一ID，若需自增整数ID可替换为ROW_NUMBER() OVER (ORDER BY Location, PairGroup)
    NEWID() AS [GENERATE-ID],
    Location,
    [Fail] AS FailTime,
    [Pass] AS PassTime
FROM RankedData
PIVOT (
    MAX(RecordTime) FOR Result IN ([Fail], [Pass])
) AS PivotedData
ORDER BY Location, PairGroup;

3. 关键说明 #

• 用MAX(RecordTime)是因为每个PairGroup+Location+Result仅对应一条记录，MAX/MIN均可正确提取时间
• 若成对规则不是按顺序一一对应（如Pass对应最近的Fail），可改用LAG()或LEAD()函数关联相邻记录调整分组逻辑
• 若不需要GUID，可替换NEWID()为CONCAT(Location, '-', PairGroup)生成业务唯一标识，或用ROW_NUMBER()生成自增整数ID

测试结果示例 #

执行上述SQL后会得到如下格式的结果：

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