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:
– this declaration should match the column type containing NULLs
declare @n int
set @n = null — by default it will be NULL, but this is a good practice
update @mytable set @n = coalesce(n, @n), n = coalesce(n, @n)
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.
