How to Fill NULL Values in a Row with the Previous Non-NULL Value in SQL Server?

How to Replace NULL Values in a Row with a Value from the Previous Known Value

Problem:

You need to replace NULL values in a row with the value from the last known value in the previous date. For example, if the date column contains values "1," "2," "3," "4," and "5," and the number column contains values "3," "NULL," "5," "NULL," and "NULL," the NULL values should be replaced with "3," "5," and "5," respectively.

Solution (SQL Server):

DECLARE @Table TABLE(
        ID INT,
        Val INT
)

INSERT INTO @Table (ID,Val) SELECT 1, 3
INSERT INTO @Table (ID,Val) SELECT 2, NULL
INSERT INTO @Table (ID,Val) SELECT 3, 5
INSERT INTO @Table (ID,Val) SELECT 4, NULL
INSERT INTO @Table (ID,Val) SELECT 5, NULL
INSERT INTO @Table (ID,Val) SELECT 6, 2


SELECT  *,
        ISNULL(Val, (SELECT TOP 1 Val FROM @Table WHERE ID < t.ID AND Val IS NOT NULL ORDER BY ID DESC))
FROM    @Table t

Explanation:

• The ISNULL function checks if the Val column is NULL. If it is, it returns the value from the subquery.
• The subquery selects the top 1 non-NULL value from the @Table table where the ID column is less than the current row's ID column.
• The ORDER BY ID DESC clause ensures that the most recent non-NULL value is returned.

The above is the detailed content of How to Fill NULL Values in a Row with the Previous Non-NULL Value in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!