时间戳

首页 > 解决方案 > 将一组行显示为单行

sql - 将一组行显示为单行

问题描述

我有一个包含以下行的表：

MsgID |       DateTime       | State | TimeDiff
---------------------------------------------
387   | 2014-09-06 21:06:27  | 2     | 43
387   | 2014-09-06 21:06:28  | 3     | 44
212   | 2014-09-06 22:16:27  | 1     | 0
212   | 2014-09-06 22:16:38  | 3     | 11
532   | 2014-09-06 23:26:27  | 1     | 0
532   | 2014-09-06 23:27:27  | 3     | 60
532   | 2014-09-06 23:27:28  | 2     | 61
215   | 2014-09-06 23:46:27  | 1     | 0
212   | 2014-09-07 22:16:27  | 1     | 0
212   | 2014-09-07 22:16:37  | 2     | 10
212   | 2014-09-07 22:16:38  | 3     | 11

我想像这样组合这些行：

MsgID |       State 1       |       State 2       |       State 3       | 
-------------------------------------------------------------------------
212   | 2014-09-06 22:16:27 | null                | 2014-09-06 22:16:38 |
532   | 2014-09-06 23:26:27 | 2014-09-06 23:27:28 | 2014-09-06 23:27:27 |
215   | 2014-09-06 23:46:27 | null                | null                |
212   | 2014-09-07 22:16:27 | 2014-09-07 22:16:37 | 2014-09-07 22:16:38 |

在此示例中，MsgID = 387 的行被忽略，因为它们没有开始条件（状态 1）。

我不知道如何解决这个问题。

标签： sqlsql-servertsqlgaps-and-islands

解决方案

您可以在如下查询中使用 PIVOT 语句。

请注意，您必须熟悉PIVOT语法。最里面的查询用于限定消息 id，以便不占用像 387 这样的 MsgId。上面带有row_number()s 的查询是为了帮助识别差距。这是类型问题的常见解决方案。在此处查看有关间隙和岛屿的有用链接

See working demo

select 
 MsgID,
 State1= [1],
 State2= [2],
 State3=[3]
from 
(
    select 
    t1.MsgID,
    t1.DateTime,
    T1.State,
    r=row_number() over ( order by t1.[DateTime])- row_number() over( partition by t1.MsgID order by t1.[Datetime])
from t t1
join 
(
    select MsgId 
    from t 
    group by MsgID 
    having min(State)=1
) t2
on t1.MsgId=t2.MsgId
) src
PIVOT
(
    max(DateTime) for State in ([1],[2],[3])
    )p

推荐阅读