I know this look complex but I'll explain:
declare @mytest table(id int not null identity(1,1) primary key,val varchar(max) null)
insert into @mytest(val)
values
(null),
(null),
('p1'),
(null),
(null),
(null),
(null),
('p2'),
(null),
(null),
('p2'),
(null),
(null),
(null),
('p3'),
(null)
;
with mycte as (
select id, val, row_number () over (order by id) as roworder
from @mytest)
, mycte2 as(
select id, val, roworder
from mycte
where roworder = 1
union all
select t.id, isnull(t.val,l.val), t.roworder
from mycte t
inner join mycte2 l on t.roworder = l.roworder+1
)
select * from mycte2
If you run that block, you will see the desired outcome.
We can ignore the test data. That's just me trying to recreate the situation.
The Common Table Expressions are what are interesting
Lets look at the first one:
with mycte as (
select id, val, row_number () over (order by id) as roworder
from @mytest)
The id's I create are in sequence, but I could not say if yours are. I hope that the data you have is in some sort of order (date, id, etc) because we need to give the list a sequential iterative number to make our live so much easier. That's what row_number does here. It puts the items in order and numbers them 1,2,3,..,,n-1,n. We can use this sequence in something called a recursive CTE.
Here is the recursive CTE:
mycte2 as(
select id, val, roworder
from mycte
where roworder = 1
union all
select t.id, isnull(t.val,l.val), t.roworder
from mycte t
inner join mycte2 l on t.roworder = l.roworder+1
)
This is where all the magic happens. In the first 'select' we take the first item in the list (roworder = 1). Then we union that with the same table joined to this CTE. Get the next item where roworder is this+1, but then it has to recalculate the recursion again, and so on until there is no item where t.roworder = l.roworder+1.
Each time we attach the next item to the current list we use ISNULL(t.val,l.val), where t represents 'this' record and l is 'last' record. If this record.val is null then use last record.val.
I don't think I explained this very well. Just try it out and see if it works ^_^