Convert one column to two column based on condition

Question

I have a table look like this:

TagName          DateTime        value
HA_06_ON    2020-07-07 08:52:14    1
HA_06_ON    2020-07-07 09:01:42    0
HA_06_ON    2020-07-07 09:02:17    1
HA_06_ON    2020-07-07 09:32:55    0
HA_06_ON    2020-07-07 09:33:21    1
HA_06_ON    2020-07-07 09:35:02    0
HA_06_ON    2020-07-07 09:35:27    1 
HA_06_ON    2020-07-07 09:35:44    0
HA_06_ON    2020-07-07 10:10:32    1
HA_06_ON    2020-07-07 10:10:40    0 

I want to convert this table into this base on value (value = 1 ==> startTime, value = 0 ==> EndTime).

TagName        StartTime                EndTime
HA_06_ON   2020-07-07 08:52:14      2020-07-07 09:01:42
HA_06_ON   2020-07-07 09:02:17      2020-07-07 09:32:55
....

I have tried to use case when on select statement but return null on each column like this

TagName           StartTime            EndTime
HA_06_ON      2020-07-07 08:57:07       NULL
HA_06_ON            NULL        2020-07-07 09:01:42
HA_06_ON      2020-07-07 09:02:17       NULL
HA_06_ON            NULL        2020-07-07 09:32:55
HA_06_ON      2020-07-07 09:33:21       NULL
HA_06_ON            NULL        2020-07-07 09:35:02

Answer 1

Just because I didn't see the sum() over option

Example

Select TagName
      ,StartTime = min(DateTime)
      ,EndTime   = max(DateTime)
 From ( 
        Select *
              ,Grp = sum(value) over (partition by TagName order by DateTime) 
         From @YourTable
      ) A
 Group By TagName,Grp

Returns

TagName     StartTime                   EndTime
HA_06_ON    2020-07-07 08:52:14.000     2020-07-07 09:01:42.000
HA_06_ON    2020-07-07 09:02:17.000     2020-07-07 09:32:55.000
HA_06_ON    2020-07-07 09:33:21.000     2020-07-07 09:35:02.000
HA_06_ON    2020-07-07 09:35:27.000     2020-07-07 09:35:44.000
HA_06_ON    2020-07-07 10:10:32.000     2020-07-07 10:10:40.000