sql - 在数据透视表的日期时间列上聚合

Question

我有一个存储员工登录信息的表

登录表的示例数据是

 EmpID  Status     [Time]
 100    SignIn    2013-03-07 11:41:44.473
 101    SignIn    2013-03-07 10:41:44.473
 100    SignOut   2013-03-07 12:41:44.473
 101    SignOut   2013-03-07 11:41:44.473
 101    SignIn    2013-03-08 11:41:44.473

我想要的结果是

EmpID   SignIn                    SignOut
 100    2013-03-07 11:41:44.473    2013-03-07 12:41:44.473
 101    2013-03-07 10:41:44.473    2013-03-07 11:41:44.473
 101    2013-03-08 11:41:44.473    NULL

我尝试使用 PIVOT

Select EmpID,[SignIn],[SignOut]
from 
(Select EmpId,status,LoginTime from Login)p
 pivot
(
 min(Logintime)
 For status in ([SignIn],[SignOut])
)pvt

但上面的查询遗漏了 Employee 101 有 SignIn Time 但 no SignOut 的最后一行值

SQLFiddle用于生成表格数据

Answer 1

您使用 PIVOT 函数的方法是正确的，您只需要一种根据 empid< 为每一行分配 row_number() 的方法、status 和 date/您可以使用以下 pivot 代码:

Select EmpID,[SignIn],[SignOut]
from 
(
  Select EmpId, status, 
    LoginTime, 
    cast(logintime as date) date,
    row_number() over(partition by empid, status, cast(logintime as date)
                      order by logintime) rn
  from Login
)p
pivot
(
  min(Logintime)
  For status in ([SignIn],[SignOut])
)pvt

参见 SQL Fiddle with Demo .

您会注意到子查询中有两个新列。一个为每一行生成一个 row_number()，第二个生成没有时间的 date。这两列都在 GROUP BY 中使用，但未显示在最终选择中。使用它们是为了让您每天(如果需要)可以为每位员工获取多行。

使用来自 sql fiddle 的@IvanG 数据的结果是:

| EMPID |              SIGNIN |             SIGNOUT |
-----------------------------------------------------
|   100 | 2013-03-07 11:41:44 | 2013-03-07 12:41:44 |
|   101 | 2013-03-07 10:41:44 | 2013-03-07 11:41:44 |
|   101 | 2013-03-08 11:41:44 |              (null) |
|   102 | 2013-04-08 12:41:44 | 2013-04-08 13:41:44 |
|   102 | 2013-04-08 16:41:44 | 2013-04-08 17:41:44 |
|   102 | 2013-04-08 19:41:44 |              (null) |