时间戳

首页 > 解决方案 > 将列拆分为每个输入/输出记录的单行

sql-server - 将列拆分为每个输入/输出记录的单行

问题描述

我想将 SwipeTime 和 SwipeDescription 拆分在同一行用于门的入口和出口。

目前这是表格的样子：

userid || SwipeDescription  ||  EmployeeName   ||  SwipeTime    
178    || Output On By Door || Alexandra Perez || 2019-08-07 08:00:56.453   
178    || Output Off By Door|| Alexandra Perez || 2019-08-07 08:01:02.300       

SELECT 
    u.userid 
    --,u.name
   ,et.name AS SwipeDescription
   ,CASE 
    WHEN scs.FullName is NULL THEN u.name
    WHEN scs.FullName is NOT NULL THEN 'C - ' + scs.FullName
    END AS EmployeeName
    ,e.LoggedTime AS SwipeTime
    ,d.name AS Door

FROM [Users] AS u
LEFT JOIN [Events] AS e ON e.RecordIndex1=u.UserID
LEFT JOIN [EventTypes] AS et on e.EventTypeID = et.EventTypeID
join [Doors] AS d ON e.RecordIndex2 = d.DoorID
LEFT join SecurityContractorSignIn as scs on scs.Badge = u.lastname and SignInTime > CONVERT(DATE, GETDATE())
WHERE LoggedTime > CONVERT(DATE, GETDATE()) and d.doorid in (32, 50, 42, 51, 33) 
ORDER BY u.name,e.LoggedTime ASC

我希望数据看起来像这样：

userid || SwipeDescriptionIn  ||    EmployeeName   ||  SwipeTimeIn  
178    || Output On By Door || Alexandra Perez || 2019-08-07 08:00:56.453 || Output Off By Door|| 2019-08-07 08:01:02.300

标签： sql-server

解决方案

   SELECT * FROM dbo.Users WITH (NOLOCK)

SELECT Main.userid,
       Main.SwipeDescription,
       Main.EmployeeName,
       Main.SwipeTime
FROM
(
SELECT U.userid,
       U.SwipeDescription,
       U.EmployeeName,
       CONVERT(VARCHAR(40),U.SwipeTime,121)+' || '
       +(SELECT SUB.SwipeDescription+' || '+CONVERT(VARCHAR(40),SUB.SwipeTime,121)
         FROM 
         (SELECT ROW_NUMBER() OVER(PARTITION BY U.userid ORDER BY T.userID,U.SwipeTime) AS RowNum,
                T.SwipeDescription,
                T.UserID,
                T.SwipeTime
         FROM Users T 
         WHERE T.UserID=U.UserID)AS Sub
         WHERE Sub.RowNum>1 ) AS SwipeTime,
         ROW_NUMBER() OVER(PARTITION BY U.userid ORDER BY U.SwipeTime DESC) AS RowNum      
FROM dbo.Users U WITH (NOLOCK)
) AS Main
WHERE Main.RowNum=1

推荐阅读