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