title: SQL Grouping columns by row
body: <p>Struggling with the SQL query to convert the data I have into the required format. I have an event log for machines and would like to associate the start and stop time and event outcome in the same row.
  I am unable to use LAG due to the version of SQLServer. Any help appreciated.</p>
  <p>current dataset:</p>
  <pre><code>+----------+----------+------------+------------------------------+---------------------+
  | MACHINE  | EVENT_ID | EVENT_CODE |           DATE_TIME          | EVENT_DESCRIPTOR    |
  +----------+----------+------------+------------------------------+---------------------+
  | 1        | 1        | 1          | 2020-08-06 14:59:26          | SCAN : START : z1 : |
  | 1        | 2        | 6          | 2020-08-06 15:00:18          | SCAN : END : z1 :   |
  | 1        | 3        | 1          | 2020-08-06 15:00:45          | SCAN : START : z1 : |
  | 1        | 4        | 5          | 2020-08-06 15:01:54          | SCAN : ABORT : z1 : |
  | 2        | 5        | 1          | 2020-08-06 15:02:15          | SCAN : START : z1 : |
  | 2        | 6        | 6          | 2020-08-06 15:05:07          | SCAN : END : z1 :   |
  | 1        | 7        | 1          | 2020-08-06 15:05:13          | NEST : START : z1 : |
  | 1        | 8        | 6          | 2020-08-06 15:05:22          | NEST : END : z1 :   |
  | 1        | 9        | 1          | 2020-08-06 15:07:17          | CUT : START : z1 :  |
  | 1        | 10       | 6          | 2020-08-06 15:10:40          | CUT : END : z1 :    |
  +----------+----------+------------+------------------------------+---------------------+
  </code></pre>
  <p>The outcome I am trying to achieve:</p>
  <pre><code>+----------+------------------------------+------------------------------+----------+
  | Machine  | SCAN:START:Z1 _TIME          | SCAN:STOP_OR_ABORT:Z1 _TIME  | OUTCOME  |
  +----------+------------------------------+------------------------------+----------+
  | 1        | Thu Aug 06 14:59:26 BST 2020 | 2020-08-06 15:00:18          | END      |
  | 1        | Thu Aug 06 15:00:45 BST 2020 | 2020-08-06 15:01:54          | ABORT    |
  | 1        | Thu Aug 06 15:02:15 BST 2020 | 2020-08-06 15:05:07          | END      |
  +----------+------------------------------+------------------------------+----------+
  </code></pre>