How to replace null value with value from the next row
You can try below sql code using co-related subquery
SELECT v1.r#,CASE WHEN value IS NOT NULL THEN v1.Value
ELSE ( SELECT TOP 1 value
FROM @value v2
WHERE v2.r# > v1.r# AND v2.[value] IS NOT NULL
) END Value
FROM @value v1
OR (without Case statement)
SELECT v1.r#,ISNULL(v1.VALUE,( SELECT TOP 1 value
FROM @value v2
WHERE v2.r# > v1.r#
AND v2.[value] IS NOT NULL
)
) AS [MonthNames]
FROM @value v1
Result
r# Value
----------
1 January
2 January
3 February
4 March
5 March
6 December
7 December
8 December
9 November
10 November
11 November
12 November
13 NULL
Next approach may help. You need additional APPLY
operator to find the first record with not NULL
value:
T-SQL:
SELECT v1.[r#], COALESCE(v1.[value], v2.[value]) AS [value]
FROM @value v1
OUTER APPLY (
SELECT TOP 1 [Value]
FROM @value
WHERE (v1.[r#] < [r#]) AND [value] IS NOT NULL
) v2
Output:
r# value
1 January
2 January
3 February
4 March
5 March
6 December
7 December
8 December
9 November
10 November
11 November
12 November
13 NULL
You can do this with window functions. Unfortunately, SQL Server doesn't support the IGNORE NULL
s option on LEAD()
, so that is not an option.
However, you can use two levels of window functions:
select v.r#, v.value,
coalesce(v.value, max(value) over (partition by next_r#)) as imputed_value
from (select v.*,
min(case when value is not null then r# end) over (order by r# desc) as next_r#
from @value v
) v
order by v.r#;
On 13 rows, the performance difference is probably not noticeable. However, this should have much better performance as the number of rows increases.
below query works in SQL Server:
;WITH CTE_Value
AS (
SELECT R#, Value
FROM @value AS T
WHERE Value IS NOT NULL
UNION ALL
SELECT t.r#, c.Value
FROM @value AS t
INNER JOIN CTE_Value AS c ON t.r# + 1 = c.r#
WHERE t.Value IS NULL
)
SELECT *
FROM CTE_Value
UNION ALL
SELECT v.*
FROM @value AS v
LEFT JOIN CTE_value AS c ON v.r# = c.r#
WHERE c.r# IS NULL
ORDER BY r#