How can I count number of non-null fields and store it in another field of a table in SQL Server

I have a table (Table1) in SQL Server 2005 that has 15 character fields: Field1, Field2, ... Field15. I also have a numeric field, called Cnt, which holds the number of non-null fields on the record. Is there an easy way to update the Cnt using a query to count the number of non-null fields on the record, in a table with about 70,000 records?
dwoolley3Asked:
Who is Participating?
 
lluddenConnect With a Mentor Commented:
UPDATE table1 SET Cnt =
    CASE WHEN Field1 IS NULL THEN 0 ELSE 1 END +
    CASE WHEN Field2 IS NULL THEN 0 ELSE 1 END +
    CASE WHEN Field3 IS NULL THEN 0 ELSE 1 END +
    CASE WHEN Field4 IS NULL THEN 0 ELSE 1 END +
    CASE WHEN Field5 IS NULL THEN 0 ELSE 1 END +
    CASE WHEN Field6 IS NULL THEN 0 ELSE 1 END +
    CASE WHEN Field7 IS NULL THEN 0 ELSE 1 END
--Repeat for each field
0
 
JestersGrindConnect With a Mentor Commented:
You could do something like this.

UPDATE Table1
SET cnt = CASE WHEN Field1 IS NOT NULL THEN 1 ELSE 0 END +
     CASE WHEN Field2 IS NOT NULL THEN 1 ELSE 0 END +
...
     CASE WHEN Field15 IS NOT NULL THEN 1 ELSE 0 END

Greg

0
 
dwoolley3Author Commented:
Works perfectly. Thx!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.