如何将数据分成两列 [英] How To Separate Data Into Two Columns

问题描述

考虑下面的表结构和示例数据 -

Consider the following table structure and sample data -

EmpID InputDateTime      StatusINOUT
-------------------------------------
1     2018-05-26 08:44     1
1     2018-05-26 08:44     2
2     2018-05-28 08:44     1
2     2018-05-28 12:44     2                   
1     2018-05-21 08:44     1
1     2018-05-21 10:44     2
2     2018-05-23 08:44     1
2     2018-05-23 08:44     2   

现在我想将 InputDateTime 列分成两列，即 INTIME(1) 和 OUTTIME(2).这背后的逻辑是 StatusInOut 为 1 的日期将是 InTime 而对于 StatusInOut 是 2，日期值将是 OUTTIME(2).

Now I want to separate column InputDateTime into two columns i.e., INTIME(1) and OUTTIME(2). The logic behind this is the date for which StatusInOut is 1 will be InTime and for StatusInOut is 2 that date value will be OUTTIME(2).

预期的输出格式如下:

Empid   INTIME(1)          OUTIME(2)
--------------------------------------------
1      2018-05-26 08:44    2018-05-26 08:44
2      2018-05-28 08:44    2018-05-28 12:44
1      2018-05-21 08:44    2018-05-21 10:44
2      2018-05-23 08:44    2018-05-23 08:44

这是我迄今为止尝试过的

This is what I have tried so far

create table #tempStatus (EmpId int, intTime datetime, sStatus int)    
insert into #tempStatus    
values(1, '2018-05-26 08:44', 1),    
    (1, '2018-05-26 08:44', 2),    
    (2, '2018-05-28 08:44', 1),    
    (2, '2018-05-28 12:44', 2),        
    (1, '2018-05-21 08:44', 1),    
    (1, '2018-05-21 10:44', 2),        
    (2, '2018-05-23 08:44', 1),    
    (2, '2018-05-23 08:44', 2)    

select EmpId, MIN(intTime) as intTime, MIN(intTime) as OutTime into #tempA from (  
select EmpId, intTime, intTime as OutTime  
from #tempStatus where sStatus = 1  
)a   
group by EmpId, intTime  

select EmpId, MAX(outTime) as outTime into #tempB from(   
select EmpId, intTime as outTime  
from #tempStatus where sStatus = 2  
)b   
group by empId,outTime     

select * from #tempA order by EmpId  

drop table #tempA  
drop table #tempB  
DROP TABLE #tempStatus

推荐答案

您需要 row_number()s &使用它们的差异进行条件聚合，这也称为Gaps and Islands问题:

You need row_number()s & use differences of them to do conditional aggregation, This also called as Gaps and Islands problem :

select empid, 
       max(case when sStatus = 1 then intTime end) as INTIME,
       max(case when sStatus = 2 then intTime end) as OUTIME
from (select t.*, 
             row_number () over ( order by inttime) as seq1,
             row_number () over (partition by empid order by inttime) as seq2
      from #tempStatus t
     ) t
group by empid, (seq1-seq2);

如果你想在 InTime 不存在时显示 OutTime，那么你可以使用子查询:

EDIT : If you want to display OutTime whenever InTime it not there, then you can use subquery :

select t.empid, 
       coalesce(INTIME, OUTIME) as INTIME,
       coalesce(OUTIME, INTIME) as OUTIME
from ( <query here> 
     ) t;

这篇关于如何将数据分成两列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！