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

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

CodePudding user response：

--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