Tags

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


T-SQL Query | [ The Remove Nulls Puzzle ]

Puzzle Description

There was a question Its very easy you just have to remove the nulls from the different cities. Check out the input and expected output below-

Pictorial presentation of the puzzle.(Pic taken from some Facebook group)

Pawan Khowal - The RemoveNull Puzzle

Pawan Khowal – The RemoveNull Puzzle

Sample Input

ID Name Salary City1 City2 City3
1 A 100 Bangalore NULL NULL
1 B 200 NULL Pune NULL
1 C 300 NULL NULL Hyderabad

Expected Output

Id Name Salary City1 City2 City3
1 A 100 Bangalore Pune Hyderabad

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) topawankkmr@gmail.com

Script

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

--

CREATE TABLE RemoveNulls
(
	 ID INT
	,Name VARCHAR(1)
	,Salary INT
	,City1 VARCHAR(50)
	,City2 VARCHAR(50)
	,City3 VARCHAR(50)
)
GO

INSERT INTO RemoveNulls(ID,Name,Salary,City1,City2,City3)
VALUES
(1,'A',100,'Bangalore',NULL,NULL),(1,'B',200,NULL,'Pune',NULL),(1,'C',300,NULL,NULL,'Hyderabad')
GO

CREATE CLUSTERED INDEX Ix_Id ON RemoveNulls(Id)
GO

--

Update Aug 06 | Solution1 & 2 – Pawan Kumar Khowal 


--

--Solution1
SELECT *
, ( SELECT TOP 1 City2  FROM RemoveNulls r1 WHERE r1.City2 IS NOT NULL AND r1.ID = X.ID ) City2
, ( SELECT TOP 1 City3  FROM RemoveNulls r1 WHERE r1.City3 IS NOT NULL AND r1.ID = X.ID ) City3
FROM
(
	SELECT r.ID,MIN(Name) Name,MIN(Salary) Salary ,MIN(City1) City1
	FROM RemoveNulls r
	WHERE City1 IS NOT NULL
	GROUP BY ID
)X

--Solution 2
SELECT
		  Id
		, MIN(name) Name
		, MIN(Salary) Salary
		, MAX(city1) City1
                , MAX(city2) City2
		, MAX(city3) City3
FROM RemoveNulls GROUP BY id

--

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

Http://MSBISkills.com

Pawan Kumar Khowal