PIVOT data in mysql

Essentially, one needs to perform a self-join to pair up movements IN with the following OUT by the same employee on the same date:

SELECT   a.EmployeeID, a.LogDate,
         a.LogTime AS In_time,
         MIN(b.LogTime) AS Out_time
FROM     my_table a LEFT JOIN my_table b ON
         a.EmployeeID = b.EmployeeID
     AND a.LogDate    = b.LogDate
     AND a.LogTime    < b.LogTime
     AND b.InOut      = 'OUT'
WHERE    a.InOut      = 'IN'
GROUP BY EmployeeID, LogDate, In_time

However, this excludes situations where there is an IN record with no corresponding OUT record. Since MySQL has no native support for FULL OUTER JOIN, one must instead use UNION to combine the above with a similar query that pairs movements OUT with the preceding IN by the same employee on the same date:

  SELECT   a.EmployeeID, a.LogDate,
           a.LogTime AS In_time,
           MIN(b.LogTime) AS Out_time
  FROM     my_table a LEFT JOIN my_table b ON
           a.EmployeeID = b.EmployeeID
       AND a.LogDate    = b.LogDate
       AND a.LogTime    < b.LogTime
       AND b.InOut      = 'OUT'
  WHERE    a.InOut      = 'IN'
  GROUP BY EmployeeID, LogDate, In_time

UNION

  SELECT   a.EmployeeID, a.LogDate,
           MAX(a.LogTime) AS In_time,
           b.LogTime AS Out_time
  FROM     my_table a RIGHT JOIN my_table b ON
           a.EmployeeID = b.EmployeeID
       AND a.LogDate    = b.LogDate
       AND a.LogTime    < b.LogTime
       AND a.InOut      = 'IN'
  WHERE    b.InOut      = 'OUT'
  GROUP BY EmployeeID, LogDate, Out_time

Having obtained this detail, one then needs to join the result with the table again in order to extract the terminals on which the movements were logged:

SELECT t.EmployeeID,
       t.LogDate,
       a.TerminalID AS In_location,
       t.In_time,
       b.TerminalID AS Out_location,
       t.Out_time
FROM (

  SELECT   a.EmployeeID, a.LogDate,
           a.LogTime AS In_time,
           MIN(b.LogTime) AS Out_time
  FROM     my_table a LEFT JOIN my_table b ON
           a.EmployeeID = b.EmployeeID
       AND a.LogDate    = b.LogDate
       AND a.LogTime    < b.LogTime
       AND b.InOut      = 'OUT'
  WHERE    a.InOut      = 'IN'
  GROUP BY EmployeeID, LogDate, In_time

UNION

  SELECT   a.EmployeeID, a.LogDate,
           MAX(a.LogTime) AS In_time,
           b.LogTime AS Out_time
  FROM     my_table a RIGHT JOIN my_table b ON
           a.EmployeeID = b.EmployeeID
       AND a.LogDate    = b.LogDate
       AND a.LogTime    < b.LogTime
       AND a.InOut      = 'IN'
  WHERE    b.InOut      = 'OUT'
  GROUP BY EmployeeID, LogDate, Out_time

) t

  LEFT JOIN my_table a ON
       a.EmployeeID = t.EmployeeID
   AND a.LogDate    = t.LogDate
   AND a.LogTime    = t.In_time
   AND a.InOut      = 'IN'

  LEFT JOIN my_table b ON
       b.EmployeeID = t.EmployeeID
   AND b.LogDate    = t.LogDate
   AND b.LogTime    = t.Out_time
   AND b.InOut      = 'OUT'

See it on sqlfiddle.