-- Online SQL Editor to Run SQL Online.
-- Use the editor to create new tables, insert data and all other SQL operations.
-- CREATE TABLE #Gaps
-- (
-- RowNumber INTEGER PRIMARY KEY,
-- TestCase VARCHAR(100) NULL
-- )
-- alter table #Gaps
-- add Status varchar(30)
-- INSERT INTO #Gaps (RowNumber, TestCase, Status) VALUES
-- (1,'Alpha','Pass'),(2,NULL,'fail'),(3,NULL,'fail'),(4,NULL,'fail'),
-- (5,'Bravo','Pass'),(6,NULL,'fail'),(7,Null,'fail'),(8,NULL,'Pass'),(9,NULL,'Pass'),
-- (10,'Charlie','fail'),(11,Null,'fail'),(12,Null,'fail')
select * from #Gaps
WITH FilledValues AS (
SELECT
t.RowNumber,
t.TestCase,
Status = (
SELECT TOP 1 t2.TestCase
FROM gaps t2
WHERE t2.RowNumber < t.RowNumber AND ISNULL(t2.TestCase, '') <> ''
ORDER BY t2.RowNumber DESC
)
FROM gaps t
WHERE ISNULL(t.TestCase, '') = ''
)
UPDATE t
SET TestCase = f.Status
FROM gaps t
JOIN FilledValues f ON t.RowNumber = f.RowNumber;
Telegram: DB developers channel