我如何聚合/透视这些数据？

Question

我有一组记录来跟踪系统可用性。示例数据：

System_ID   Event    DateOfEvent
1           Down     2010-05-01 13:20:10
1           Up       2010-05-01 13:25:19
1           Down     2010-05-05 10:12:12
1           Up       2010-05-06 14:10:16
2           Down     2010-05-05 20:22:22
2           Up       2010-05-06 23:20:26

如何将此数据转换为以下布局？

System_Id   DowntimeStart         DowntimeInSeconds
1           2010-05-01 13:20:10   309
1           2010-05-05 10:12:12   100684
2           2010-05-05 20:22:22   97084

Answer 1

你可以试试这个：

Select et1.System_ID, et1.[Date] as DateDown, et2.[Date] as DateUp, DATEDIFF(s, et1.[Date], et2.[Date]) as DownForSeconds
    From EventTable et1
    Left Join EventTable et2 On et1.System_ID = et2.System_ID and et2.[Event] = 'Up'
Where 
et1.[Event] = 'Down'
and et2.[Date] = (Select Top 1 [Date] From EventTable Where System_ID = et2.System_ID and [Date] > et1.[Date])