SQL Puzzle: Find rows with more NULL values in Columns

Question: Find the rows that have more than one NULL value across its columns. check below the sample data and expected output.

Input Data:

Expected Output:

Data Script:

CREATE Table Puzzle2
(
	ID NVARCHAR(15),
	Col1 NVARCHAR(15) NULL,
	Col2 NVARCHAR(15) NULL,
	Col3 NVARCHAR(15) NULL
)

INSERT INTO Puzzle2 VALUES
	(1,'a',NULL,'b'),
	(2,NULL,'b','c'),
	(3,NULL,NULL,NULL),
	(NULL,'1',NULL,'0'),
	(5,'X',NULL,NULL)

SELECT * FROM Puzzle2

CopyCopy

Please try to answer the question, if you still not able to get it click below show button to see the answer.

;WITH CTE 
AS
(
	SELECT *,ROW_NUMBER() OVER(ORDER BY ID) AS RNo FROM Puzzle2
),
CTE2 
AS (
	SELECT RNo, COUNT(1)  as CNT
	FROM 
	(
		SELECT ID,RNo FROM CTE
		UNION ALL
		SELECT Col1, RNo FROM CTE
		UNION ALL
		SELECT Col2,RNo FROM CTE
		UNION ALL
		SELECT Col3, RNo FROM CTE
	) A WHERE ID IS NULL 
	GROUP BY RNo HAVING COUNT(1) >1
)
SELECT ID,Col1,Col2,Col3 
FROM CTE WHERE RNO IN (SELECT DISTINCT RNo FROM Cte2)
        

• Newer

• Older