I have a tsql query like that:
x
DECLARE @TABLE TABLE(
id int primary key identity(1,1),
code int );
INSERT INTO @TABLE VALUES (1),(NULL),(NULL),(NULL),(2),(NULL),(NULL),(NULL),(3),(NULL),(NULL),(NULL);
SELECT
id ,
code
FROM @TABLE T1
My result :
id code
1 1
2 NULL
3 NULL
4 NULL
5 2
6 NULL
7 NULL
8 NULL
9 3
10 NULL
11 NULL
12 NULL
I want to change null value with null value’s last one numeric value. I want to see that:
1 1
2 1
3 1
4 1
5 2
6 2
7 2
8 2
9 3
10 3
11 3
12 3
But i dont want to use while loop. How can i do it ?
Advertisement
Answer
If the values are increasing, use a cumulative max:
select t.*,
max(code) over (order by id) as imputed_code
from @table t;
If the code is not strictly increasing, then you can do this in two steps:
select t.*, max(code) over (order by grp) as imputed_code
from (select t.*,
count(code) over (order by id) as grp
from @table t
) t;
Given that this is a table variable, I’m guessing that you don’t really want to update it. But if you do:
with toupdate as (
<one of the above queries>
)
update toupdate
set code = imputed_code
where code is null;