This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to replace the NULL value with the previous NON-Null value.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 |
Id Code ----------- ---- 1 ABC 2 XYZ 3 NULL 4 NULL 5 PQR 6 NULL 7 ERT 8 NULL |
Expected Output:
|
1 2 3 4 5 6 7 8 9 10 |
Id Code Previous_NonNullCode ----------- ---- -------------------- 1 ABC ABC 2 XYZ XYZ 3 NULL XYZ 4 NULL XYZ 5 PQR PQR 6 NULL PQR 7 ERT ERT 8 NULL ERT |
Create a table with data:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE tbl_Values ( Id INT ,Code VARCHAR(3) ) GO INSERT INTO tbl_Values VALUES (1,'ABC'),(2,'XYZ'),(3,NULL),(4,NULL) ,(5,'PQR'),(6,NULL),(7,'ERT'),(8,NULL) GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 |
;WITH CTE AS ( SELECT *,ROW_NUMBER() OVER (ORDER BY Id) rnk FROM tbl_Values ) SELECT Id ,Code ,(SELECT TOP 1 Code FROM CTE a WHERE a.rnk = (SELECT MAX(b.rnk) FROM CTE b WHERE b.rnk <= c.rnk AND b.Code IS NOT NULL)) Previous_NonNullCode FROM CTE c |
Please try the different solution for this puzzle and share it via comment...
