Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


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

Advertisements