How To Pivot This Table In Sql

I came across this issue when trying to make use of this 'active users' table from Microsoft NAV. What I got asked to do is a Dashboard in Power BI or other tool that will allow th

Solution 1:

This is TSQL code and will run on an Azure database. I think this will get you the results you are looking for.

DECLARE@ActiveUsersLogASTABLE
      (
          sessionId     INT
         ,EventDateTime DATETIME
         ,EventType     VARCHAR(50)
      );

INSERTINTO@ActiveUsersLog
     (
         sessionId
        ,EventDateTime
        ,EventType
     )
VALUES
     (350, '2017-07-01 01:00', 'Logon')
    ,(350, '2017-08-01 02:00', 'Logoff')
    ,(351, '2017-07-01 02:00', 'Logon')
    ,(351, '2017-08-01 03:00', 'Logoff')
    ,(350, '2017-09-01 01:00', 'Logon')
    ,(350, '2017-09-01 02:00', 'Logoff');

WITH cte_logon
    AS (
           SELECT aul.sessionId
                 ,aul.EventDateTime
                 ,seq =RANK() OVER (PARTITIONBY aul.sessionId ORDERBY aul.EventDateTime)
             FROM@ActiveUsersLogAS aul
            WHERE aul.EventType ='Logon'
       )
    ,cte_logoff
    AS (
           SELECT aul.sessionId
                 ,aul.EventDateTime
                 ,seq =RANK() OVER (PARTITIONBY aul.sessionId ORDERBY aul.EventDateTime)
             FROM@ActiveUsersLogAS aul
            WHERE aul.EventType ='Logoff'
       )
SELECT o.sessionId
      ,LogonTime = o.EventDateTime
      ,LogoffTime = f.EventDateTime
  FROM cte_logon AS o
      LEFTOUTERJOIN cte_logoff AS f
          ON o.sessionId = f.sessionId
             AND o.seq = f.seq;

If you are only looking for active users then you would add this where clause: (make sure to remove the semicolon at the end of the code above before adding a where clause)

where f.EventDateTime isnull

Solution 2:

Without knowing the flavour of SQL, I can only give you basic advice.

By performing an inner join with both sides lining to the same table (aliasing appropriately) matching on the session_id of both sides, you can then filter Event Type on one of the aliased tables to equal Logon and Event Type on the other aliased table to match Logoff.

This method should work on just about any form of SQL I've used, as it's core functionality of SQL.