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?