1

Possible Duplicate:
How to count in SQL all fields with null values in one record?

I have table with five columns: 

Name     DOB    Email      phone   jobtitle
abc     null   a@c.com      null       null
bbc     null    null        null       null

How do I write a query so that I can find the number of null columns in a row?

(e.g. Row 1 is having 3 null value, and row 2 is having 4 null values.)

I am using SQL Server 2008.

Community
  • 1
  • 1
vikas
  • 336
  • 2
  • 5
  • 16

2 Answers2

2

The naive way:

SELECT CASE WHEN Name IS NULL THEN 1 ELSE 0 END +
    CASE WHEN DOB IS NULL THEN 1 ELSE 0 END +       
    CASE WHEN Email IS NULL THEN 1 ELSE 0 END +       
    CASE WHEN phone IS NULL THEN 1 ELSE 0 END +       
    CASE WHEN jobtitle IS NULL THEN 1 ELSE 0 END

But I wouldn't want to write 76 of these. So how about the dynamic way (untested, but something along these lines):

DECLARE @SQL NVARCHAR(4000);    
SET @SQL = 'SELECT theTable.ID, ' + 
    STUFF((SELECT '+ CASE WHEN ' + QUOTENAME(COLUMN_NAME) + 
        ' IS NULL THEN 1 ELSE 0 END' 
        FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = 'theTable' FOR XML PATH('')) , 1 , 1 , '') 
    + ' FROM theTable';    
EXEC(@SQL);
lc.
  • 113,939
  • 20
  • 158
  • 187
-2

I think the only way is to specify each of your columns

select sum(case when item1 is null then 1 else 0 end
          +case when item2 is null then 1 else 0 end
          +case when item3 is null then 1 else 0 end
          +case when item4 is null then 1 else 0 end
          +case when item5 is null then 1 else 0 end
          +case when item6 is null then 1 else 0 end
          ) as grandtotalnulls
  from yourtable
Vytalyi
  • 1,637
  • 3
  • 20
  • 33
  • There are ways to do this without writing 76 `CASE` expressions (and how is this different from solutions that came before yours?). Please [look at the duplicate](http://stackoverflow.com/questions/9079037/how-to-count-in-sql-all-fields-with-null-values-in-one-record). – Aaron Bertrand Sep 20 '12 at 13:34