group clock in and clock out attendance query

I have Attendance table:

How to query this table into:

where the ClockIn/ClockOut is based on AttType (when ‘IN’ then ClockIn, when ‘OUT’ then ClockOut)

I tried this query :

SELECT EmpID,
       DateEntry,
       CASE
           WHEN AttType = 'IN' THEN DateEntry
           ELSE NULL
       END AS ClockIn,
       CASE
           WHEN AttType = 'OUT' THEN DateEntry
           ELSE NULL
       END AS ClockOut
FROM Attendance
GROUP BY EmpID,
         DateEntry; 

but the results was:

how to get the correct result? anyone can help me? thank you.

>Solution :

This can be done using the conditional aggregation based on the date part using the aggregate function MAX() :

SELECT DATE(DateEntry) AS DateEntry,
       EmpID,
       MAX(CASE
           WHEN AttType = 'IN' THEN TIME(DateEntry)
           ELSE NULL
       END) AS ClockIn,
       MAX(CASE
           WHEN AttType = 'OUT' THEN TIME(DateEntry)
           ELSE NULL
       END) AS ClockOut
FROM Attendance
GROUP BY EmpID,
         DATE(DateEntry); 

Results :

DateEntry   EmpID   ClockIn     ClockOut
2024-01-01  E-0001  08:00:02    17:01:00
2024-01-01  E-0002  07:59:02    17:00:07

Demo here