如何在SQL中旋转临时表 [英] How to pivot temp table in sql

问题描述

      Date             Time        Mode ID
    2017-01-01  13:00:00.0000000    3   10
    2017-01-01  14:00:00.0000000    1   10
    2017-01-01  15:00:00.0000000    3   10
    2017-01-01  15:30:00.0000000    1   10

这是一个临时表.我只想将时间列显示为2列，其中模式= 3的列为1列，模式= 3的列为其他列.

This is a temp table.I just want to display time column as 2 columns,1 column with mode =3 and other with mode=1.

这是一个临时表.我只想要以下输出:

This is a temp table.I just want the below output:

          Date         InTime(Mode-3)    OutTime(Mode-1)     ID

         2017-01-01   13:00:00.0000000  14:00:00.0000000    10

         2017-01-01   15:00:00.0000000  15:30:00.0000000    10

推荐答案

此方法有效，具体取决于数据和数据类型/模式(以及表的名称是否为 timeTable ):

This works, depending on the data and datatypes/schema (and if the name of the table is timeTable):

    SELECT DATE, time AS 'InTime(Mode-3)',
      (SELECT TOP 1 time FROM timeTable
          WHERE mode = 1 
            AND id = outerTable.id 
            AND date = outerTable.date 
            AND time > outerTable.time 
          ORDER BY date, time) AS 'OutTime(Mode-1)',
      ID
    FROM timeTable AS outerTable 
    WHERE mode = 3

• outerQuery仅选择即时 mode = 3
innerQuery中的
• 选择下一个超时时间，该时间对应于所选的及时时间，并且仅返回第一个时间.由于按日期和时间排序，因此应该是下一个. 仅使用给定的数据进行测试

• the outerQuery only selects the in-times mode = 3
• in innerQuery selects the next out-time, that correspondes to the selected in-time, and only returns the first one. since ordered by date and time, it should be the next one. Only tested with your given data

输出:

    Date       |  InTime(Mode-3)     |   OutTime(Mode-1)   |  ID
---------------|---------------------|---------------------|------
   2017-01-01  |  13:00:00.0000000   |  14:00:00.0000000   |  10
   2017-01-01  |  15:00:00.0000000   |  15:30:00.0000000   |  10

仅供参考:
我使用了此表模式

Just for reference:
I used this table schema

 CREATE TABLE timeTable(
     date DATE,
     time TIME,
     mode INTEGER,
     id INTEGER
 );

更新:
随着时间-差异:

Update:
With time - difference:

SELECT *, DATEDIFF(MINUTE,INTIME,OUTTIME) AS [DIFFERENCE] FROM (
    SELECT [DATE], [time] AS INTIME,
      (SELECT TOP 1 [time] FROM timeTable
          WHERE [mode] = 1 
            AND [id] = outerTable.id 
            AND [date] = outerTable.date 
            AND [time] > outerTable.time 
          ORDER BY [date], [time]) AS OUTTIME,
      [ID]
    FROM [timeTable] AS outerTable 
    WHERE [mode] = 3
) WholeData

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