How to merge consecutive record pairs over a table?

Question

I have a table where I need to merge consecutive record pairs (when ordered by id, data and from). Note that for pairs being merged, the first row's prevfrom is NULL and the second row's nextto is NULL.

id  data from        to          prevfrom    nextto
1   a    2015-01-01  2015-01-05  NULL        2015-01-10
1   a    2015-01-10  2015-01-14  2015-01-05  NULL
1   a    2015-01-15  2015-01-20  NULL        2015-01-22
1   a    2015-01-30  2015-02-04  2015-01-25  NULL
2   c    2015-01-01  2015-01-05  NULL        2015-01-10
2   c    2015-01-05  2015-01-10  2015-01-01  NULL

So in this case, I would want to merge rows 1&2, 3&4, and 5&6, and discard helper columns prevfrom and nextto. As a result of the merge, each row would have from value of the first record in the pair, and to value of the second record in the pair. Like so:

id  data from        to          
1   a    2015-01-01  2015-01-14  
1   a    2015-01-15  2015-02-04  
2   c    2015-01-01  2015-01-10  

I can do this by doing a SELECT and a JOIN inside a WHILE loop until I run out of record pairs (pseudo code):

WHILE table exists DO:  
BEGIN
  INSERT INTO #temp
    SELECT TOP 2 * FROM table ORDER BY [id],[data],[from]  --get a pair
  INSERT INTO result            --join #temp with itself to merge rows
    SELECT t1.[id],t1.[data],t1.[from], t2.[to]
    FROM #temp AS t1
    JOIN #temp AS t2
      ON t1.[from]<t2.[from]    --later record joined on earlier record
  DELETE FROM table             --update original table
    IF record exists IN #temp
  TRUNCATE TABLE #temp          --empty temp table for next insertion
END  

However I feel that doing an ORDER BY in every iteration is inefficient. Is there a simpler way to do it?

Answer 1

Basically, use common table expressions to isolate and number rows in 2 groups with one helper column NULL in each, and then join the two groups on row numbers.

WITH cte1 AS (
    SELECT [id], [data], [from] , ROW_NUMBER() OVER(ORDER BY [id], [data], [from]) AS [row]
        FROM #temp3
        WHERE [prevfrom] IS NULL),
    cte2 AS (
    SELECT [to], ROW_NUMBER() OVER(ORDER BY [id], [data], [from]) AS [row]
        FROM #temp3
        WHERE [nextto] IS NULL)
SELECT t1.id, t1.data, t1.from, t2.to
    FROM cte1 AS t1
    JOIN cte2 AS t2
    ON t1.[row]=t2.[row];