Tags

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


T-SQL Query | [Movie Puzzle] – Write a query which will remove the unwanted null values from the table. Please check sample input and expected output for details.

Sample Input

ID Name Typed
1 P NULL
1 NULL Q

Expected Output

Id Name Typed
1 P Q

Rules/Restrictions

  • The solution should be should use “SELECT” statement or “CTE”.
  • Send your solution to pawankkmr@gmail.com
  • Do not post you solution in comments section

Script

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


--Create a table called PQ..

CREATE TABLE PQ
(
ID INT
,Name VARCHAR(10)
,Typed VARCHAR(10)
)

--Insert data
INSERT INTO PQ(ID,Name,Typed) VALUES  (1,'P',NULL) , (1,NULL,'Q')

--Verify data
SELECT * FROM  PQ

Update May 14 | Solution


--

/************   SOLUTION 1    | Pawan Kumar Khowal     ****************/


SELECT p.Id,p.Name, (SELECT r.Typed from PQ r WHERE p.ID = r.Id AND r.Typed IS NOT NULL) Typed FROM PQ p WHERE p.Name IS NOT NULL



/************   SOLUTION 2    | Pawan Kumar Khowal     ****************/

SELECT Id, MIN(CASE WHEN name IS NOT NULL THEN name ELSE NULL END) Name
, MIN(CASE WHEN typed IS NOT NULL THEN typed END) Typed 
FROM pq 
GROUP BY Id


/************   SOLUTION 3   | Pawan Kumar Khowal     ****************/

;WITH CTE1 AS
(
	SELECT ID, Name , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM pq WHERE Name IS NOT NULL
)
,CTE2 AS
(
	SELECT ID, Typed , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM pq WHERE Typed IS NOT NULL
)
SELECT c1.ID, Name, Typed FROM CTE1 c1 INNER JOIN CTE2 c2 ON c1.rnk = c2.rnk


--

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

Keep Learning

http://MSBISkills.com