sql - SQL根据值将值移动到新列

Question

我们有一个如下例所示的表格：

TS                          |Description        |Value
--------------------------------------------------------
2020-06-12 14:13:12.253     |Machine Status     |Event 4
2020-06-12 14:12:56.250     |Machine Status     |Event 3
2020-06-12 14:12:26.250     |Machine Status     |Event 2
2020-06-12 14:11:06.253     |Machine Status     |Event 1
2020-06-12 14:10:12.253     |Machine Status     |Event 4
2020-06-12 14:09:56.250     |Machine Status     |Event 3
2020-06-12 14:09:26.250     |Machine Status     |Event 2
2020-06-12 14:08:06.253     |Machine Status     |Event 1

我们想根据事件转置时间戳

TS_Event4                   |TS_Event3                   |TimeDiff
------------------------------------------------------------------------
2020-06-12 14:13:12.253     |2020-06-12 14:12:56.250     |Event4-Event3
2020-06-12 14:10:12.253     |2020-06-12 14:09:56.250     |Event4-Event3
....

现在是棘手的部分......我根据时间戳对表格进行排序，并且需要找到第一个“event4”。下一个“event3”（不总是下一行）需要在它旁边的列中。

标签： sqlsql-serveraggregate-functions

Answer 1

我认为你想要条件聚合：

select max(case when value = 'Event 4' then ts end),
       max(case when value = 'Event 3' then ts end),
       datediff(second,
                max(case when value = 'Event 1' then ts end),
                max(case when value = 'Event 2' then ts end)
               )
from t;

编辑：

对于修改后的问题，您可以group by通过计算每个事件的序号来添加一个键：

select max(case when value = 'Event 4' then ts end),
       max(case when value = 'Event 3' then ts end),
       datediff(second,
                max(case when value = 'Event 1' then ts end),
                max(case when value = 'Event 2' then ts end)
               )
from (select t.*, row_number() over (partition by value order by ts) as seqnum
      from t
     ) t
group by seqnum;