This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to fill the gap of missing codes.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Code ---- A NULL NULL NULL NULL NULL M NULL NULL NULL N NULL NULL |
Expected Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
OldCode NewCode ------- ------- A A NULL A NULL A NULL A NULL A NULL A M M NULL M NULL M NULL M N N NULL N NULL N |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE MissingData ( Code CHAR(1) ) GO INSERT INTO MissingData CodeS ('A'),(NULL),(NULL),(NULL),(NULL),(NULL) ,('M'),(NULL),(NULL),(NULL),('N'),(NULL),(NULL) GO |
Solution:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
;WITH CTE AS ( SELECT p.Code ,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rrnk FROM MissingData p ) SELECT c.Code AS OldCode ,( SELECT d.Code from CTE d WHERE d.rrnk = (Select max(e.rrnk) from CTE e Where e.rrnk <= c.rrnk and (e.Code != '' )) ) NewCode FROM CTE c |
Please try the different solution for this puzzle and share it via comment...
