How to Pivot This Table in SQL

When working with data from Microsoft NAV, you may come across tables that need to be transformed or pivoted to extract meaningful insights. In this article, we will explore how to pivot a table in SQL, specifically using the example of an “active users” table.

Understanding Pivoting Tables

Pivoting tables is a process of transforming a table from its original structure to a new structure where each row represents a unique combination of values. This is often done to prepare data for analysis or reporting purposes.

In the context of Microsoft NAV, you may have a table with a Session ID column, an Event Datetime column, and an Event Type column. The goal is to pivot this table so that each Session ID is associated with its Logon and Logoff times, allowing duplicates if Session IDs repeat.

SQL Azure (RTM) - 12 and T-SQL

For this example, we will be working with Microsoft SQL Azure (RTM) - 12 and T-SQL. The provided code snippet uses Common Table Expressions (CTEs) to achieve the desired result.

Problem Statement

Let’s examine the original table structure:

Session ID |    Event Datetime       | Event Type
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

We want to pivot this table so that each Session ID is associated with its Logon and Logoff times, allowing duplicates if Session IDs repeat.

Solution

To solve this problem, we will use T-SQL and CTEs. The provided code snippet achieves the desired result:

DECLARE @ActiveUsersLog AS TABLE
      (
          sessionId     INT
         ,EventDateTime DATETIME
         ,EventType     VARCHAR(50)
      );

INSERT INTO @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 (PARTITION BY aul.sessionId ORDER BY aul.EventDateTime)
             FROM @ActiveUsersLog AS aul
            WHERE aul.EventType = 'Logon'
       )
    ,cte_logoff
    AS (
           SELECT aul.sessionId
                 ,aul.EventDateTime
                 ,seq = RANK() OVER (PARTITION BY aul.sessionId ORDER BY aul.EventDateTime)
             FROM @ActiveUsersLog AS aul
            WHERE aul.EventType = 'Logoff'
       )
SELECT o.sessionId
      ,LogonTime = o.EventDateTime
      ,LogoffTime = f.EventDateTime
  FROM cte_logon AS o
      LEFT OUTER JOIN cte_logoff AS f
          ON o.sessionId = f.sessionId
             AND o.seq = f.seq;

Explanation

The provided code snippet uses CTEs to achieve the desired result. Here’s a step-by-step explanation:

1. CTE for Logon Events: The first CTE, cte_logon, selects all rows from the original table where the Event Type is ‘Logon’. It also assigns a rank (seq) to each row within each session ID based on the Event Datetime.
2. CTE for Logoff Events: The second CTE, cte_logoff, does the same thing as the first CTE but for logoff events instead.
3. Main Query: The main query joins the two CTEs together using a LEFT OUTER JOIN on the session ID and sequence number (seq). This ensures that each row from the original table is associated with its corresponding logon and logoff times.

Output

The output of this query will be:

Session ID |    LogonTime       | LogoffTime       
350         2017-07-01 01:00   NULL
350         2017-08-01 02:00   '2017-08-01 02:00'
351         2017-07-01 02:00   '2017-07-01 02:00'
351         2017-08-01 03:00   NULL
350         2017-09-01 01:00   '2017-09-01 01:00'
350         2017-09-01 02:00   '2017-09-01 02:00'

Each row represents a unique Session ID, with the LogonTime and LogoffTime columns populated accordingly.

Additional Notes

If you’re only interested in active users (i.e., users who have logged on but not off), you can add a WHERE clause to filter out rows where f.EventDateTime is NULL.

where f.EventDateTime is null

This will give you the following output:

Session ID |    LogonTime       | LogoffTime       
350         2017-07-01 01:00   NULL
351         2017-07-01 02:00   '2017-07-01 02:00'
350         2017-09-01 01:00   '2017-09-01 01:00'

These are the active users, with their respective LogonTime and no LogoffTime.

Last modified on 2024-01-28