SQL PUZZLE | Remove NULLs Puzzle | SQL Interview Question

In this puzzle you have to remove the NULL from the multiple columns – Vals1, Vals2, Vals3, Vals4 and Vals5, And then generate two columns – Col1 and Col2. For any row we shall have maximum 2 non NULL values. For more details please see the sample input and expected output.

Sample Input

Id Vals1 Vals2 Vals3 Vals4 Vals5
a NULL 2 NULL 4 NULL
b 1 NULL NULL 2 3
c 4 NULL NULL 3 NULL
d NULL NULL 3 NULL 5

Expected Output

Id Col1 Col2
a 2 4
b 1 3
c 4 3
d 3 5

Script – DDL and INSERT Sample Data

 ```-- CREATE TABLE FixNULLs ( Id VARCHAR(10) ,Vals1 INT ,Vals2 INT ,Vals3 INT ,Vals4 INT ,Vals5 INT ) GO INSERT INTO FixNULLs VALUES ('a',NULL,2,NULL,4,NULL), ('b',1,NULL,NULL,2,3), ('c',4,NULL,NULL,3,NULL), ('d',NULL,NULL,3,NULL,5) GO SELECT * FROM FixNULLs GO -- ```

SOLUTION – 1

 ```-- SELECT Id, COALESCE(Vals1,Vals2,Vals3,Vals4) Col1, COALESCE(Vals5,Vals4,Vals3,Vals2) Col2 FROM FixNULLs GO -- ```

Output – 1

 ```-- Id Col1 Col2 ---------- ----------- ----------- a 2 4 b 1 3 c 4 3 d 3 5 (4 rows affected) -- ```

SOLUTION – 2

 ```-- ;WITH CTE AS ( SELECT *,ROW_NUMBER() OVER (PARTITION BY Id ORDER BY (SELECT NULL)) rnk FROM FixNULLs t UNPIVOT ( a FOR Vals IN ([Vals1],[Vals2],[Vals3],[Vals4],[Vals5]) )u ) SELECT Id,MAX([1]) [Col1] ,MAX([2]) [Col2] FROM CTE PIVOT ( MAX(a) FOR rnk IN ([1],[2]) )w GROUP BY Id -- ```

Output – 2

 ```-- Id Col1 Col2 ---------- ----------- ----------- a 2 4 b 1 2 c 4 3 d 3 5 (4 rows affected) -- ```

SOLUTION – 3

 ```-- SELECT t.Id,Col1,Col2 FROM FixNULLs t CROSS APPLY ( SELECT MAX(IIF(rnk = 1,Val,'')) Col1, MAX(IIF(rnk = 2,Val,'')) Col2 FROM ( SELECT val, Vals, ROW_NUMBER() OVER (PARTITION BY Id ORDER BY (SELECT NULL)) rnk FROM ( VALUES (t.[Vals1], Vals1), (t.[Vals2], Vals2) , (t.[Vals3], Vals3), (t.[Vals4], Vals4) , (t.[Vals5], Vals5) ) v(val, Vals) WHERE Val IS NOT NULL )v )u -- ```

Output – 3

 ```-- Id Col1 Col2 ---------- ----------- ----------- a 2 4 b 1 2 c 4 3 d 3 5 (4 rows affected) -- ```

Enjoy 🙂

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/