时间戳

首页 > 解决方案 > 在一段时间内透视/转换 SQL 结果集

sql-server - 在一段时间内透视/转换 SQL 结果集

问题描述

请运行以下代码以查看结果集。

IF OBJECT_ID('tempdb..#temptable') IS NOT NULL DROP TABLE #temptable

CREATE TABLE #temptable ( [VehicleKey] int, [RegistrationKey] int, [RegisteredOperatorKey] int, [StartDate] date )
INSERT INTO #temptable
VALUES
( 700090, 550983, 207287, N'2018-07-31T00:00:00' ), 
( 700090, 1513637, 276879, N'2018-12-31T00:00:00' ), 
( 700090, 1513637, 341604,  N'2019-02-28T00:00:00' )

SELECT * FROM #temptable

如何将结果集转换为如下所示？

在此处输入图像描述

请注意，钥匙始终是 VehicleKey。请注意 StartDate 记录如何随时间变化的车辆。

标签： sql-servertsqldynamicpivot

解决方案

这将为您提供所需的输出。SELECT * FROM #temptable用下面的代码替换语句

;WITH tt AS (
    SELECT *,
        ROW_NUMBER() OVER (partition by VehicleKey ORDER BY StartDate) AS Seq
    FROM #tempTable
    )
SELECT t1.VehicleKey,
    t1.RegistrationKey As OldRegistrationKey,
    t2.RegistrationKey As NewRegistrationKey,
    t1.RegisteredOperatorKey AS OldRegisteredOperatorKey,
    t2.RegisteredOperatorKey AS NewRegisteredOperatorKey,
    t1.StartDate as OldStartDate,
    t2.StartDate as NewStartDate
FROM tt t1
INNER JOIN tt t2
    ON t1.VehicleKey = t2.VehicleKey
    AND t1.Seq = t2.Seq - 1

推荐阅读