What will be the sql code for - rows that have NULLs in 'multiple columns' [closed]

Say for example there are 4 columns and we only want rows to be displayed where more than 1 column has Null value

Image

Solution 1:

The most straightforward (since you're going to have to scan the entire table anyway) is to not try to do anything clever that takes advantage of certain columns being in a specific data type and just add up how many values in each row are null:

;WITH x AS
(
  SELECT col1, col2, col3, col4,
    NumberOfNulls = 0 + 
      CASE WHEN col1 IS NULL THEN 1 ELSE 0 END + 
      CASE WHEN col2 IS NULL THEN 1 ELSE 0 END + 
      CASE WHEN col3 IS NULL THEN 1 ELSE 0 END + 
      CASE WHEN col4 IS NULL THEN 1 ELSE 0 END
  FROM dbo.table_name
)
SELECT col1, col2, col3, col4
WHERE NumberOfNulls > 1;

I made up the table and column names here; I didn't look at your screenshot because please don't post screenshots.