Tags

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


SQL Puzzle | The Remove NULL Puzzle

Puzzle Statement

This puzzle was asked to me via gmail. Here you have to find sum of previous value to current value. Please check sample input and expected output.

The Remove NULL Puzzle

The Remove NULL Puzzle

Sample Input

100 NULL NULL NULL
NULL 200 NULL NULL
NULL NULL 300 NULL
NULL NULL NULL 400

Expected Output

100 200 300 400

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE FourColumns
(
	 ID1 SMALLINT
	,ID2 SMALLINT
	,ID3 SMALLINT
	,ID4 SMALLINT
)
GO

INSERT INTO FourColumns(ID1,ID2,ID3,ID4)
VALUES
(100,NULL,NULL,NULL),(NULL,200,NULL,NULL),(NULL,NULL,300,NULL),(NULL,NULL,NULL,400)
GO
--

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

SOLUTION 1 | Using MAX Function

--

SELECT 
	 MAX(ID1) ID1
	,MAX(ID2) ID2
	,MAX(ID3) ID3
	,MAX(ID4) ID4
FROM FourColumns

--

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

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements