SQL Filling NULL values with Preceding Non-NULL values - Option 1

SQL Filling NULL values with Preceding Non-NULL values - Option 1.sql

;With 
CTE1 AS ( 
SELECT Id, ProductName , ROW_NUMBER() OVER(ORDER BY Id ) AS RowId FROM @MyTable ), 
CTE2 AS ( 
  SELECT ProductName, RowId, RowId As MyGroup 
  FROM CTE1 WHERE [Group] IS NOT NULL 
  Union All SELECT A.ProductName, A.RowId, B.MyGroup 
  FROM CTE1 A 
  INNER JOIN CTE2 B 
     On A.RowId = B.RowId + 1 Where A.[Group] IS NULL ) 
SELECT Id, RowId, MyGroup , DENSE_RANK() OVER(ORDER BY MyGroup) AS [Group] 
FROM CTE2 
ORDER BY Id OPTION (Maxrecursion 0);