Aggregate on Datetime Column for Pivot into 4 columns

Question

I need help with pivoting a column from one into 4, to look like this:

Going on from this post Aggregate on Datetime Column for Pivot

I got the first part done with pivoting from one column into 2 columns: Sign in and Sign out. But I need to have 4 columns with sign in and sign out for the same day but different times, since employee punches in and out for lunch but it's the same day. I'm looking for one row per employee ID and day with 4 different time stamps of that same day.

Answer 1

Edit the value in column status at the beginning. Then apply to this output the steps you followed for the 2-columns solution.

select
EmpId
, SignIn1
, SignOut1
, SignIn2
, SignOut2
from (
  select EmpId,  [Time], Status = Status +          convert(varchar(10),row_number() over (partition by EmpId, Status order by [time])) from login) q
    pivot (min([Time]) for Status in ([SignIn1],[SignOut1],[SignIn2],[SignOut2])) p