Filling the missing gaps(Sql Server 2005) [Set based using recursive CTE]

Question

I have a table

MissingData

1
10
NULL
NULL
22
NULL

The desired output will be

MissingData

1
10
10
10
22
22

i.e. the nulls will be filled up by the previous value until a new value is appearing.

I can solve this by using loop but my requirement is to solve it by CTE in which I am not so comfortable as of now.

Thanks

Answer 1

The order of the values in your list is implicitly part of the problem. This order must be stored as part of the data in the source table, otherwise the problem can't be solved reliably, even in a loop. I've added an "ordinal" column to represent the missing ordering.

declare @orderedset table(ordinal int not null, value int null)

insert into @orderedset(ordinal, value) values (1, 100)
insert into @orderedset(ordinal, value) values (2, 10)
insert into @orderedset(ordinal, value) values (3, NULL)
insert into @orderedset(ordinal, value) values (4, NULL)
insert into @orderedset(ordinal, value) values (5, 22)
insert into @orderedset(ordinal, value) values (5, NULL);

WITH fillvalues AS
(
    SELECT ordinal, value FROM @orderedset
    WHERE value IS NOT NULL
)
SELECT 
(
    SELECT value FROM fillvalues
    WHERE fillvalues.ordinal = 
    (
        SELECT MAX(ordinal) 
        FROM fillvalues 
        WHERE ordinal <= unfilled.ordinal
    )
) As filledvalue
FROM @orderedset unfilled