Merge two consecutive rows into a column

Question

Assuming that, I have a table something like this :

EmployeeCode	EntryType	TIME
ABC200413	IN	8:48AM
ABC200413	OUT	4:09PM
ABC200413	IN	4:45PM
ABC200413	OUT	6:09PM
ABC200413	IN	7:45PM
ABC200413	OUT	10:09PM

Now I want to convert my data something like this :

EmployeeCode	IN_TIME	OUT_TIME
ABC200413	8:48AM	4:09PM
ABC200413	4:45PM	6:09PM
ABC200413	7:45PM	10:09PM

Is there any way I can achieve this using SQL server query?

Thanks in advance.

You can refer to this link https://stackoverflow.com/questions/19858079/how-to-convert-rows-to-columns-in-oracle — Koushik Roy, Apr 21 '21 at 10:42
You are assuming that the rows in your table have a natural order. How do you intend to ensure this? — Mark Roberts, Apr 21 '21 at 10:42
The link posted by Koushik Roy doesn't answer the question, because our poster here can have any number of IN's and OUT's per customer. The supplied answer assumes a constant number of three rows. — Mark Roberts, Apr 21 '21 at 10:45
@MarkRoberts We managed this from the code level, so everything in this table will be in this pattern. — Munnacse18, Apr 21 '21 at 10:48

score 6 · Accepted Answer · answered Apr 21 '21 at 10:55

Provided mytable contains only valid pairs of in / out events

select EmployeeCode, 
  max(case EntryType when 'IN' then TIME end ) IN_TIME,
  max(case EntryType when 'OUT' then TIME end ) OUT_TIME
from ( 
   select EmployeeCode, EntryType, TIME,
     row_number() over(partition by EmployeeCode, EntryType order by TIME) rn
   from mytable
   )t
group by EmployeeCode, rn
order by EmployeeCode, rn

Otherwise a kind of clean-up is required first.

score 0 · Answer 2 · answered Apr 21 '21 at 10:56

One solution that may work is using inner joins .. as you may have multiple In & Out records.

Select A.EmployeeCode,
       Min(TIME) IN_TIME,
       (Select Max(A2.TIME) From Attendance A2 Where A2.EmployeeCode = A.EmployeeCode And A2.EntryType = 'OUT') OUT_TIME
From Attendance  A
Where A.EntryType = 'IN'
Group By A.EmployeeCode

So, The main query get the max Out time for each employee & the inner query get the min In time. That solution supposes that at least each employee has one IN record

score 0 · Answer 3 · answered Apr 21 '21 at 11:43

Assuming there are always pairs of IN/OUT, you can use LEAD to get the next value

select
  EmployeeCode, 
  TIME as IN_TIME,
  nextTime AS OUT_TIME
from ( 
   select *,
     lead(case EntryType when 'OUT' then TIME end) over (partition by EmployeeCode order by TIME) nextTime
   from mytable
) t
where EntryType = 'IN';

Merge two consecutive rows into a column

3 Answers3