michael michael -4 years ago 119
SQL Question

Pivot temp table without aggregate function

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


This is a temp table.I want to pair these data.I just want the below output:

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

13:00:00.0000000 14:00:00.0000000

15:00:00.0000000 15:30:00.0000000


I need to get the In Out timings in same row as above

Answer Source

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

    SELECT 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)' 
    FROM timeTable AS outerTable 
    WHERE mode = 3
  • 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

Output:

         InTime(Mode-3)      |     OutTime(Mode-1) 
  ---------------------------|---------------------------
       13:00:00.0000000      |    14:00:00.0000000
       15:00:00.0000000      |    15:30:00.0000000

Just for reference:
I used this table schema

 CREATE TABLE timeTable(
     date DATE,
     time TIME,
     mode INTEGER,
     id INTEGER
 );
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download