Split Single Row into Multiple Rows Based on Two Timestamp Columns-CodePudding

I want to split the following records:

DeviceId    StartTime                  EndTime
------------------------------------------------------------
1001        2022-02-12 07:27:00.000    2022-02-12 16:23:00.000
1002        2022-02-14 03:36:00.000    2022-02-14 04:36:00.000

Into:

DeviceId    Timestamp                   State
-------------------------------------------------
1001        2022-02-12 07:27:00.000     1    
1001        2022-02-12 16:23:00.000     2
1002        2022-02-14 03:36:00.000     1
1002        2022-02-14 04:36:00.000     2

The new State column should be based on whether the Timestamp is a StartTime ( = 1), or an EndTime ( = 2).

What would be the t-sql query to achieve this ?

CodePudding user response：

You may unpivot the table using VALUES table value constructor:

SELECT t.DeviceId, v.[Timestamp], v.[State]
FROM YourTable t
CROSS APPLY (VALUES
   (t.StartTime, 1),
   (t.EndTime, 2)
) v ([Timestamp], [State])