SQL Key propagation query

I have a SQL Server table structured as follows: RowNumber, Level, PartNumber, ProductNumber and Key. I need to propagate the Key value (when it is not NULL) from a row to all subsequent rows with a higher level (greater than the current row's level) until it encounters a row with a level that is equal to or less than the starting row's level (see the points below).

What I expect:

• This problem is a parent-child structure (lower level acts as parent and higher levels as child).

• In the example, line 3 is a level 2 part and there are parts below that row with levels 3 and 4, they belong to the part in line 3.

• Therefore, the key in line 3 must be propagated from there to the row 7, even if the parts in that range have a key.

• Line 8 is another level 2, but there is no higher level below it, so the key remains in row 8.

• Line 11, part is level 3 and there is a part below level 4, the key must be propagated in row 12.

• If there was a key in line 2 (for example), all the lines below would receive that key, because levels 2, 3 and 4 belong to 1.

The code below didn't get exactly what I expected, for example, on line 10 it propagated the key from line 8 (but the part number from line 10 belongs to line 9, not 8. Then should not propagate from 8 to 10). I would appreciate it if someone could guide me on what I could try differently, a better approach. Thanks!

 SELECT
        C1.[RowNumber],
        C1.[Level], 
        C1.[PartNumber],
        C1.[ProductNumber],
        COALESCE(
            C1.[Key],
            (SELECT TOP 1 C2.[Key]
             FROM TableName C2 
             WHERE C2.[Level] < C1.[Level]
               AND C2.[RowNumber] < C1.[RowNumber]
               AND C2.[Key] IS NOT NULL
             ORDER BY C2.[Level] DESC, C2.[RowNumber] DESC
            )
        ) AS [Key]
FROM 
        TableName C1

It seems you have to do this row by row using CTE:

SELECT  *
INTO #data
FROM    (
    VALUES  (1, 0, N'099', N'A', NULL)
    ,   (2, 1, N'001', N'A', NULL)
    ,   (3, 2, N'012', N'A', N'012_A')
    ,   (4, 3, N'003', N'A', NULL)
    ,   (5, 4, N'094', N'A', N'094_A')
    ,   (6, 4, N'005', N'A', NULL)
    ,   (7, 3, N'006', N'A', N'006_A')
    ,   (8, 2, N'007', N'A', N'007_A')
    ,   (9, 2, N'008', N'A', NULL)
    ,   (10, 3, N'009', N'A', NULL)
    ,   (11, 3, N'010', N'A', N'010_A')
    ,   (12, 4, N'011', N'A', NULL)
    ,   (13, 2, N'012', N'A', NULL)
) t (RowNumber,Level,PartNumber,ProductNumber,[Key])

;WITH cte AS (
    SELECT  RowNumber, Level, Level AS MasterLevel, PartNumber, ProductNumber
    ,   [key]
    FROM    #data
    WHERE   RowNumber = 1
    UNION ALL
    SELECT  d.RowNumber, d.Level
    ,   CASE WHEN master.flag = 1 THEN c.MasterLevel ELSE d.Level END
    ,   d.PartNumber, d.ProductNumber
    ,   CASE WHEN master.flag = 1 THEN c.[Key] ELSE d.[Key] END
    FROM    cte c
    INNER JOIN #data d
        ON  d.rowNumber = c.RowNumber + 1
    CROSS APPLY (
            SELECT  CASE WHEN c.MasterLevel < d.Level AND c.[key] IS NOT NULL THEN 1 ELSE 0 END AS flag
        ) master
    )
SELECT  *
FROM    cte

I introduce a MasterLevel column which holds current level from which the keys are copied. This is either taken from previous row or current if current row level is less or equals to master level.

Then based on the MasterLevel and key, i add a flag which says if a key copy should occur or not, and create the values appropriately.

Output:

Btw, this structure you're using ain't standard, normally one uses concrete IDs to figure out child/parent relations, which does away the need of doing this level-walk.