Tags
Complex SQL Challenges, Complex TSQL Challenge, Interview Qs.SQL SERVER Questions, Interview Questions on SQL, InterviewQuestions, InterviewQuestions for SQL, Learn complex SQL, Learn SQL, Learn T-SQL, PL/SQL Challenges, Puzzles, Queries for SQL Interview, SQL, SQL 2012, SQL Challenge, SQL Challenges, SQL pl/sql puzzles, SQL Puzzles, SQL SERVER Interview questions, SQL SERVER2005/2008, SQL Sudoku, SQLSERVER, T SQL Puzzles, T-SQL Challenge, Tough SQL Challenges, Tough SQL Puzzles, TSQL, TSQL Challenge, TSQL Challenges, TSQL Interview questions, TSQL Queries
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
Kiran Kumar said:
select id, max(name),max(Typed) from PQ group by id
LikeLiked by 1 person
Pawan Kumar Khowal said:
Excellent Kiran !!
LikeLike
Akshay Barakoti said:
SELECT DISTINCT P.ID,ISNULL(P.Name,Q.Name) AS NAME,ISNULL(P.Typed,Q.Typed) AS TYPED FROM PQ P
INNER JOIN PQ Q
ON P.ID=Q.ID
WHERE ISNULL(P.Name,Q.Name) IS NOT NULL AND ISNULL(P.Typed,Q.Typed) IS NOT NULL
LikeLiked by 1 person
Pawan Kumar Khowal said:
Nice !!
LikeLike
praveen (@PraveenY6000) said:
SELECT p2.Name,p1.Typed FROM PQ p1 inner join PQ p2 on p1.ID=p2.ID where p2.Name is not null and p1.Typed is not null
LikeLike
Pawan Kumar Khowal said:
Hi Praveen, We also need ID column in the output. Thanks
LikeLike
sree99 (@BhavyaSreeT) said:
How About this???
select p.ID, p.Name, q.Typed From pq p join pq q
on p.ID =q.ID and p.name is not null and q.Typed is not null
LikeLiked by 1 person
Adam H Hamroyev said:
SELECT
Id,
Name = MIN(Name),
Typed = MIN(Typed)
FROM pq
GROUP BY Id;
LikeLiked by 1 person
Hema Krishna Kumari said:
How it works in Real time is my question…..I am a beginner with SQL ,what my doubt is there is a person P with ID 1 and typed has No valued and at the same time these is typed Q with no person …So in this process of Removing unwanted Nulls what we are forcing in Output is making a person P to Typed Q….Does this sound ok ? in real time….I mean I have a sort of confusion which I want to get it cleared…Could you please explain better with real time example.
LikeLiked by 1 person
mdarwich12 said:
–the solution above fails with several rows — this one doesn’t
with source(id,name,typed) as (
select 1,’P’,NULL
union all
select 1,NULL,’Q’
union all
select 1,null,’R’
),
CTE1 AS
(
SELECT ID, Name , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM source WHERE Name IS NOT NULL
),
CTE2 AS
(
SELECT ID, Typed , ROW_NUMBER() OVER (ORDER BY (SELECT 1)) rnk FROM source WHERE Typed IS NOT NULL
)
SELECT
coalesce(cte1.id,cte2.id) as id,
CTE1.name,
cte2.typed
from CTE1
full outer join CTE2
on cte1.id = CTE2.id and CTE1.rnk = CTE2.rnk
LikeLike