sql - MSSQL 使用双列将 2 行合并为 1

Question

我可能没有正确搜索但找不到这个。我想将 2 行的数据合并为 1 行，列数加倍。首先选择具有最新时间戳的那个。然后，如果结果超过 2 行，则只取前 2 行。下面的示例数据和预期结果

来源:

ID  Height  Color   Type    TimeStamp
1   6       Green   Plant   2017-09-23
1   24      Red     Tree    2017-09-15
1   33      Pink    Shrub   2016-05-14
2   12      Blue    Car     2017-03-21
2   88      Pink    Truck   2017-11-22

期望的结果:

ID  Height1 Color1  Type1   Height2 Color2  Type2
1   6       Green   Plant   24      Red     Tree
2   88      Pink    Truck   12      Blue    Car

这几天一直在纠结这个问题。帮忙!!!

Answer 1

OUTER APPLY的方式:

SELECT TOP 1 WITH TIES  y.ID,
                        y.Height as Height1,
                        y.Color as Color1,
                        y.[Type] as [Type1],
                        t.Height2,
                        t.Color2,
                        t.[Type2]
FROM YourTabel y
OUTER APPLY (
    SELECT TOP 1    Height as Height2,
                    Color as Color2,
                    [Type] as [Type2]
    FROM YourTabel 
    WHERE ID = y.ID AND [TimeStamp] < y.[TimeStamp]
    ORDER BY [TimeStamp] DESC
) as t
ORDER BY ROW_NUMBER() OVER (PARTITION BY y.ID ORDER BY y.[TimeStamp] DESC)

输出:

ID  Height1 Color1  Type1   Height2 Color2  Type2
1   6       Green   Plant   24      Red     Tree
2   88      Pink    Truck   12      Blue    Car

对于 SQL Server 2012 及更高版本，您可以使用 LAG :

SELECT TOP 1 WITH TIES  y.ID,
                        y.Height as Height1,
                        y.Color as Color1,
                        y.[Type] as [Type1],
                        LAG(y.Height) OVER (PARTITION BY y.ID ORDER BY y.[TimeStamp] ASC) Height2,
                        LAG(y.Color) OVER (PARTITION BY y.ID ORDER BY y.[TimeStamp] ASC) Color2,
                        LAG(y.[Type]) OVER (PARTITION BY y.ID ORDER BY y.[TimeStamp] ASC) [Type2]
FROM YourTabel y
ORDER BY ROW_NUMBER() OVER (PARTITION BY y.ID ORDER BY y.[TimeStamp] DESC)