Recently, I came across a requirement to take a resultset containing NULL fields and fill a column's NULL value with the last known non-null value of the given column. This is best illustrated via an example. If we have the results:

n
--
1
null
null
2
null
null
null
null
3
4

The desired output would be:

1
1
1
2
2
2
2
3
4

After testing a few methodologies, the fastest solution utilized an update with a variable.

Suppose, we have this table definition:

declare @mytable table (n int)

The statement to "fill the gaps" would be:[sourcecode language="sql"]-- this declaration should match the column type containing NULLsdeclare @n intset @n = null -- by default it will be NULL, but this is a good practiceupdate @mytable set @n = coalesce(n, @n), n = coalesce(n, @n)[/sourcecode]To explain, the above statement assigns a new value to the variable @n if the current row's "n" column is not null. Otherwise it assigns the value of @n. Next it will assign the value of @n to column "n", IF column "n" is null. This solution will scan every row (in the order of insertion into your table or based on a clustered index if one exists) and replace NULL values in the column of your choice with the last non-NULL value in the sequence of values.

Review the definition of COALESCE in Books Online if you're not clear.