sql-server - SQL Server : split a column value into two separate columns based on another column

Question

我有一个表访问:

logId   empid   empname  inout   tim  
----------------------------------------------------    
230361   0100     XYZ     0      2015-08-01 10:00:03
230362   0106     XYZ     0      2015-08-01 10:30:00
230363   0100     XYZ     1      2015-08-01 12:00:00

记录每个员工的上下类时间。 inout=0 表示输入，inout=1 表示输出

我想从此表创建如下表

empid  empname     timIn                 timOut
-------------------------------------------------------------    
0100     XYZ       2015-08-01 10:00:03   2015-08-01 12:00:00
0106     XYZ       2015-08-01 10:30:00

首先我尝试了如下情况:

select 
    empid, empname, inout,
    case when inout = 0 then tim end as 'timIn',
    case when inout = 1 then tim end as 'timout'

但是 NULL 是一个问题，结果是

0100  xyz       2015-08-01 10:00:03   NULL
0100  xyz       NULL                  2015-08-01 12:00:00

其次我尝试了PIVOT，但问题是我必须使用聚合函数。我需要所有进出时间，但无法汇总。

有没有其他方法可以得到想要的结果？

Answer 1

您可以使用APPLY ，结合 TOP 1 和正确的 ORDER BY 来获取每个 in 事件之后的下一个 out 事件

SELECT  i.empID,
        i.empname,
        TimeIn = i.tim,
        TimeOut = o.tim
FROM    Access AS i
        OUTER APPLY
        (   SELECT  TOP 1 tim
            FROM    Access AS o
            WHERE   o.EmpID = i.EmpID
            AND     o.InOut = 1
            AND     o.tim > i.tim
            ORDER BY o.Tim
        ) AS o
WHERE   i.InOut = 0;

因此，您只需选择所有 in 事件(别名为 i 的表)，然后对于每个 in 事件，查找下一个 out 事件，如果没有，则超时字段将为空。

---

完整的工作示例

DECLARE @Access TABLE (LogID INT NOT NULL, EmpID CHAR(4) NOT NULL, empname VARCHAR(50), InOut BIT NOT NULL, tim DATETIME2 NOT NULL);
INSERT @Access (LogID, EmpID, empname, InOut, tim)
VALUES
    (230361, '0100', 'XYZ', 0, '2015-08-01 10:00:03'),
    (230362, '0106', 'XYZ', 0, '2015-08-01 10:30:00'),
    (230363, '0100', 'XYZ', 1, '2015-08-01 12:00:00');

SELECT  i.empID,
        i.empname,
        TimeIn = i.tim,
        TimeOut = o.tim
FROM    @Access AS i
        OUTER APPLY
        (   SELECT  TOP 1 tim
            FROM    @Access AS o
            WHERE   o.EmpID = i.EmpID
            AND     o.InOut = 1
            AND     o.tim > i.tim
            ORDER BY o.Tim
        ) AS o
WHERE   i.InOut = 0;