SQL query to put data of the next row to the current row

Question

I am trying to write a query that will put the date from one row to another.

2021-04-23 07:52:41    (start time)
2021-04-23 08:14:04    (stop time)
2021-04-23 08:14:04    (start time)
2021-04-23 09:52:49    (stop time)
2021-04-23 09:52:49    (start time)
2021-04-23 09:53:44    (stop time)
2021-04-23 09:53:44    (start time)
2021-04-23 09:53:59    (stop time)

For example I have selected rows with dates. The pairs are start and stop datetimes.

For a report, I am trying to do something like this

Start                      Stop
--------------------------------------------------
2021-04-23 07:52:41.000    2021-04-23 08:14:04.000
2021-04-23 08:14:04.000    2021-04-23 09:52:49.000

The number of pairs can differ. I was thinking about changing all rows into columns, but it doesn't really work for me in report designer.

Is there way to combine every two rows into single rows? (for example I have 14 rows and want to have pairs in 7 rows)

Edit: unfortunately I am working with SQL Server 2008 and it doesn't support lead and lag. But I managed to get the desired result through this join

select ta.*, tb.*
from table1 ta
left join table1 tb on ta.ID = tb.ID -1
where (ta.ID % 2 = 0)

Answer 1

Using your sample data, you can get the next row's date using lead and assign a row number using row_number. Then you can select only the odd rows to give you a start and corresponding stop:

select [start], [stop] from
(
    select [date] [Start],
      Row_Number() over(order by [date]) rn,
      Lead([date]) over(order by [date]) [Stop]
    from T
)T
where rn % 2=1