Rearranging values in temp table

Hello everyone :)

I have a temp table that looks like this (as a result of two seperate inserts into it):

RowNumber starttime endtime
---- ---- ----
1 l 0 l NULL
2 l 360 l NULL
3 l 720 l NULL
4 l 1020 l NULL
5 l 1380 l NULL
6 l NULL l 180
7 l NULL l 540
8 l NULL l 900
9 l NULL l 1200
10 l NULL l 1440

I need some help getting it to look like this (with No NULL cell values)!

RowNumber starttime endtime
---- ---- ----
1 l 0 l 180
2 l 360 l 540
3 l 720 l 900
4 l 1020 l 1200
5 l 1380 l 1440

PLEASE help I have been looking at this for the past 3 days and can't figure it out! :oI just hate RFH problems like this! Oh well...

Has your instructor covered correlated sub-queries yet?

-PatP|||select t1.starttime, t2.endtime
from (select RowNumber, starttime from tbl where endtime is null) t1
inner join (select RowNumber endtime from tbl where starttime is null) t2
on t1.RowNumber = t2.RowNumber - 5|||How do you know which ones belong to which? Does the table look exactly like you just said? After 10 are there any valus with the second column filled and the third column NULL? How did the data get like this in the first place? Are you going to be able to fix that, or will this be ongoing?|||Thank you for your responses! :)

I tried your suggestion rdjabarov:

select t1.starttime, t2.endtime
from (select RowNumber, starttime from #end where endtime is null) t1
inner join (select RowNumber endtime from #end where starttime is null) t2
on t1.RowNumber = t2.RowNumber - 5

and I get an "Invalid column name 'RowNumber'." (RowNumber is a valid column in the temp table).

The temp table became this way because I wanted to store a result set from one query into one column of a temp table, and then store another result set from another query into the second column of the temp table.

The number of result sets for each query will always be the same. For example if I have 14 rows, 1 will link to 8, 2 will link to 9, 3 to 10, etc.

I did try another method: I created one temp table that contained the start times (with RowNumber 1-5), I then created another temp table that contained only the endtimes (with RowNumber 1-5) and joined both tables on the RowNumber and then inserted those values into another temp table (3 temp tables in total!).

I would like to use rdjabarov's solution (with 1 temp table) for improved performance...can you tell me why I am getting the:

"Invalid column name 'RowNumber'." error?

THANK YOU!! :D|||select t1.starttime, t2.endtime
from #end t1
inner join #end t2
on t1.RowNumber = t2.RowNumber - 5
and t2.starttime is null
and t1.endtime is null|||GREAT! That worked! I am indebted to you rdjabarov!!!

Thank you very much for responding as quickly as you did!!! :D