如何在SQL中透视该表 [英] How to pivot this table in SQL

问题描述

当尝试使用Microsoft NAV中的活动用户"表时遇到了此问题.我被要求做的是Power BI中的仪表板或其他工具，该工具将使我工作的公司可以随时了解有多少用户在NAV系统上处于活动状态.我在Power BI中发现了一个DAX公式可以解决这个问题-但它需要进行以下表转换:

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 the company I work for to understand how many users are active on the NAV system at any point in time. I found a DAX formula in Power BI that could do the trick - but it requires the following table transformation:

我正试图在SQL Management Studio中做到这一点:

I am trying to do this in SQL Management Studio:

我的SQL Server是Microsoft SQL Azure(RTM)-12.

My SQL Server is Microsoft SQL Azure (RTM) - 12.

我如何使用下面的结构来透视下面的NAV表，知道会话ID可能会在此行的后面重复?

How could I pivot this NAV table below with the following structure knowing that Session ID might repeat further down the line?

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

最终结果将是每个会话ID对应其登录和注销时间(如果像本例一样重复会话ID，则允许重复)

The final result would be each Session ID against their Logon and Logoff time (allowing duplicates if Session ID repeats like in this example)

非常感谢.

最佳

安德烈

推荐答案

这是TSQL代码，将在Azure数据库上运行.我认为这将为您提供所需的结果.

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

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;

如果您只寻找活动用户，则可以添加以下where子句:(确保在添加代码之前，在上述代码末尾删除分号)

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 is null

这篇关于如何在SQL中透视该表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！