Select the count of columns with values with specific name for the columns

Issue

My table(car_mileage) structure is like this.

No of cars column (car_Number) will be dynamically populate and will vary depending on different persons.

I need the sql query which gives the count of car_Number columns whose values are more than 0 for a particular name.

example for Joseph – count is 10
for Jose – count is 8

Please Help

Solution

--I think it can do in multiple solution. one of this is 
-- SQL Server statement
SELECT name,age,County,
CASE WHEN car_1>0 then 1 else 0 END + CASE WHEN car_2>0 THEN 1 ELSE 0 END
 + ... + CASE WHEN car_n>0 THEN 1 ELSE 0 END AS ColumnsCountIsMoreThenZero
FROM MyTable

Answered By – hasan

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0