SQL Server:根据另一列将列值分割为两个独立列

I have a table Access:

我有一个表访问:

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

which records each employee's in time and out time. inout=0 means in and inout=1 means out

记录每个员工的时间和时间。inout=0表示in, inout=1表示out

I would like to create a table as below from this table

我想从这个表创建一个如下所示的表

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

First I tried case as follows:

首先，我尝试了以下案例:

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

But NULLs were a problem the result was

但结果是，零是一个问题

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

Second I tried PIVOT, but the problem was I had to use an aggregate function. I need all in-out times and cannot take an aggregate of that.

第二，我尝试了主元，但问题是我必须使用聚合函数。我需要所有的输入时间，不能把它的总和。

Is there any alternative way to get the desired result?

有没有其他的方法来获得想要的结果?

3 个解决方案

#1

You can use APPLY, in conjunction with TOP 1 and the correct ORDER BY to get the next out event after each in event

您可以使用APPLY，结合TOP 1和正确的顺序，在每个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;

So you are simply selecting all in events (table aliased i), then for each in event, finding the next out event, if there is not one, then the time out field will be null.

因此，您只需选择events(表别名为i)中的所有事件，然后为每个in event查找下一个out事件(如果没有的话)，那么time out字段将为null。

FULL WORKING EXAMPLE

完整的工作示例

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;

#2

So what I think you want to do is find the first time out after each time in. The following SQL should do that.

所以我想你想要做的是在每次约会之后找到第一次约会。下面的SQL应该这样做。

Select 
empid,
empname,
tim as timein 
(select top 1 tim 
 from my_table outTimes 
 where outTimes.inout = 1 and 
       outTimes.empid = inTimes.empid and 
       outTimes.tim > inTimes.tim 
 orderby outTimes.tim asc
) as timeout
from my_table inTimes
when inout=0

The critical bit here is the orderby asc and the top 1. This is what gives you the next time in the table.

这里的关键部分是orderby asc和top 1。这就是你下次在桌子上的原因。

#3

update: Based on comment that I should improve this query to take all dates data and not just last date's data, updated query simply includes a new date column

更新:基于我应该改进这个查询以获取所有日期数据而不仅仅是最近日期的数据的评论，更新的查询仅仅包含一个新的日期列

select empid,empname,d,[0] as [timin],[1] as [timOut]
from
(select empid,empname, cast(tim as DATE)as d,inout,tim from tbl) s
pivot
(max(tim) for inout in ([0],[1]))p

updated fiddle link http://sqlfiddle.com/#!6/f1bc7/1

更新小提琴链接http://sqlfiddle.com/ ! 6 / f1bc7/1

try PIVOT query like this:

尝试这样的PIVOT查询:
select empid,empname,[0] as [timin],[1] as [timOut]
from
(select empid,empname,inout,tim from tbl) s
pivot
(max(tim) for inout in ([0],[1]))p
added SQL fiddle link http://sqlfiddle.com/#!6/6c3bf/1

添加了SQL fiddle链接http://sqlfiddle.com/#!6/6c3bf/1

#1