There is a sophisticated "Gaps and Islands" technique from Itzik Ben_Gan to do this.But here is a more step-by-step way.Most people like/understand CTEs better so I used that method.Hopefully at the very least it will help you get going.DECLARE @table1 TABLE ( time time, button char(3) )insert into @table1select '9:00','Off' union allselect '10:00','On' union allselect '11:00','On' union allselect '12:00','On' union allselect '13:00','Off' union allselect '14:00','On' union allselect '15:00','On'SELECT * FROM @table1;WITH buttons_all AS ( SELECT time, button, ROW_NUMBER() OVER(ORDER BY time) AS row_num FROM @table1 ons), buttons_first_ons AS ( SELECT time, row_num FROM buttons_all b_ons WHERE button = 'on' AND EXISTS(SELECT 1 FROM buttons_all b_all WHERE b_all.row_num = b_ons.row_num - 1 AND b_all.button = 'off')),buttons_first_offs AS ( SELECT time, row_num FROM buttons_all b_offs WHERE button = 'off' AND EXISTS(SELECT 1 FROM buttons_all b_all WHERE b_all.row_num = b_offs.row_num - 1 AND b_all.button = 'on'))SELECT b_ons.time AS [On], ISNULL(b_offs.time, (SELECT MAX(time) FROM buttons_all)) AS [Off]FROM buttons_first_ons b_onsLEFT OUTER JOIN buttons_first_offs b_offs ON b_offs.row_num = (SELECT MIN(row_num) FROM buttons_first_offs b_offs2 WHERE b_offs2.row_num > b_ons.row_num)