T-SQL Query | [ Fill Missing Value Puzzle ] – In this puzzle we have to fill the missing values in the sample input table . Please check out the sample input and expected output for details.

Sample Input

 Value ayValue A 1 23 21 22 B 34 31 89 C 222 10

Expected Output

 Value ayValue A 1 A 23 A 21 A 22 B 34 B 31 B 89 C 222 C 10

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

 ``` --Create Table CREATE TABLE [dbo].[MissingValue] ( [Value] [varchar](1) NULL, [ayValue] [int] NULL ) GO --Insert Data INSERT INTO [dbo].[MissingValue](Value,ayValue) VALUES ('A',  1), ('',   23), ('',   21), ('',   22), ('B',  34), ('',   31), ('',   89), ('C',  222), ('', 10) --Verify Data SELECT Value,ayValue FROM [MissingValue] ```

Update May 14 | Solution

 ``` -- /************ SOLUTION 1 ****************/ ;WITH CTE2 AS ( SELECT p.Value,p.ayValue ,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rrnk FROM [dbo].[MissingValue] p ) SELECT c.Value,c.ayValue,c.rrnk, ( SELECT d.Value from CTE2 d WHERE d.rrnk = (Select max(e.rrnk) from CTE2 e Where e.rrnk <= c.rrnk and (e.value != '' )) ) NewValue FROM CTE2 c -- ```

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

http://MSBISkills.com