Get corresponding rows in single line result?

Question

Given the following data, how would I get the desired result below?

Timestamp | Session ID | Event   | Name
------------------------------------------
08:15     | 89         | Login   | Scott
08:16     | 89         | Edit    | Scott
08:16     | 92         | Login   | John
08:17     | 92         | Refresh | John
08:23     | 89         | Logout  | Scott
08:28     | 92         | Logout  | John
08:30     | 96         | Login   | Scott
08:37     | 96         | Logout  | Scott

Desired Result (essentially a list of session durations):

Name  | Login | Logout
------------------------
Scott | 8:15  | 8:23
John  | 8:16  | 8:28
Scott | 8:30  | 8:37

edit: extended sample data and results to avoid some confusion.

---

The query I'm actually needing to develop is much more complex . I just thought this would give me a good jumpstart on one of the logic hurdles. Since I know everyone will want to know what I've tried, here is my current, embarrassing, iteration from the actual structure...

SELECT 
        SessionId,
        SAMLData_Organization, 
        (Select TimeCreated FROM ens.messageheader h1,HS_Message.XMLMessage m1 WHERE h1.SessionId = h3.SessionId and m1.name = 'XDSB_QueryRequest') as RequestRecieved,
        (Select TimeCreated FROM ens.messageheader h1,HS_Message.XMLMessage m1 WHERE h1.SessionId = h3.SessionId and m1.name = 'XDSB_QueryResponse') as ResponseSent
FROM 
        ens.messageheader h3,HS_Message.XMLMessage m3
WHERE SessionId IN (SELECT Distinct SessionId FROM ens.messageheader WHERE TimeCreated >= '2016-08-22 08' AND TimeCreated < '2016-08-22 17')

Things I'm trying to tackle:

1. Join ENS.MessageHeader and HS_Message.XMLMessage
2. Get the TimeCreated value for messages of type XDSb_QueryRequest
3. Get the TimeCreated value for the corresponding XDSb_QueryResponse using the SessionId as a common value.
4. Return results as Organization | RequestReceived | ResponseSent

Answer 1

Pure LEFT SELF JOIN method

SELECT
    li.Name
    ,li.Timestamp as Login
    ,lo.Timestamp as LogOut
FROM
    TableName li
    LEFT JOIN TableName lo
    ON li.[Session ID] = lo.[Session ID]
    AND lo.Event = 'Logou'
WHERE
    li.Event = 'Login'

LEFT SELF JOIN with aggregation

SELECT
    li.Name
    ,li.Timestamp as Login
    ,MIN(lo.Timestamp) as LogOut
FROM
    TableName li
    LEFT JOIN TableName lo
    ON li.Name = lo.Name
    AND lo.Timestamp > li.Timestamp
    AND lo.Event = 'Logou'
WHERE
    li.Event = 'Login'
GROUP BY
    li.Name
    ,li.Timestamp

The top one is good because it constrains per SessionId so you can see a per session look. The bottom works well if session Id is not unique to the name and login/logout pair you are looking for.

Per your answer it should be able to be written like this as well:

SELECT
    li.SAMLData_Organization,
    li.SessionId,
    m1.TimeCreated as RequestRecieved,
    m2.TimeCreated as ResponseSent
FROM
    ens.messageheader h1
    INNER JOIN HS_Message.XMLMessage m1
    ON h1.MessageBodyId = m1.id
    and m1.name = 'XDSb_RetrieveRequest'
    LEFT JOIN HS_Message.XMLMessage m2
    ON h1.MessageBodyId = m2.id
    and m2.name = 'XDSb_RetrieveResponse'
ORDER BY
    h1.SessionId DESC