Posts tagged ‘coalesce’

November 26, 2010

T-SQL coalescing column values across rows

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:

-- 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.

Tags: ,