Tags

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


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 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

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 Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ 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/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com