Assuming that it is only this table you are interested in then this simple technique would work
;with x as
(
SELECT id, hasvalue = CASE WHEN ISNULL([Name],'') <> '' THEN 1 ELSE 0 END FROM #test
UNION ALL
SELECT id, hasvalue = CASE WHEN ISNULL(mob,'') <> '' THEN 1 ELSE 0 END FROM #test
UNION ALL
SELECT id, hasvalue = CASE WHEN ISNULL([address],'') <> '' THEN 1 ELSE 0 END FROM #test
UNION ALL
SELECT id, hasvalue = CASE WHEN ISNULL(gender,'') <> '' THEN 1 ELSE 0 END FROM #test
UNION ALL
SELECT id, hasvalue = CASE WHEN ISNULL(Dob,'') <> '' THEN 1 ELSE 0 END FROM #test
)
select id, SUM(hasvalue) from x group by id
Comments:
- I duplicated your table into
#test
, replace that with your table name
- You need to use
UNION ALL
- it won't work with just
UNION
(as duplicates will be removed)
- Your data had a blank (not NULL) date - I trust you have not stored that date in a varchar column!