Tags

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


SQL Puzzle | The Remove Duplicate UOM Puzzle

Puzzle Statement

This is cool puzzle. this comes to my mind while working on a recent assignment. Here you to find distinct values of ID & UOM. We have to remove null and blank values of UOM if they have multiple UOMs against single ID. E.g. In Case of ID = 1 We have to remove NULL UOM but we have to consider ID=5 as it has only 1 row. Please go through the sample input and expected output for details.

Sample Input

ID UOM
1 Electric
1 Electric
1 NULL
2
2 Water
3 Waste
3 Recycling
4 NULL
4 NULL
5

Expected Output

ID UOM
1 Electric
2 Water
3 Waste
3 Recycling
4 NULL
5

Rules/Restrictions – The solution should be should use “SELECT” statement or “CTE”.

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


--



CREATE TABLE Measure
(
	 ID INT
	,UOM VARCHAR(100)
)
GO

INSERT INTO Measure
VALUES
(1,'Electric'),
(1,'Electric'),
(1,NULL),
(2,''),
(2,'Water'),
(3,'Waste'),
(3,'Recycling'),
(4,NULL),
(4,NULL),
(5,'')
GO



--

SOLUTION 1 | Using Ranking Function

--


SELECT ID, UOM1 UOM
FROM
(
	SELECT ID, CASE WHEN (UOM) = '' OR UOM IS NULL THEN MAX(UOM) OVER (PARTITION BY ID) ELSE UOM END UOM1
	FROM Measure
)r
GROUP BY ID, UOM1


--

Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

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