sql - Count how many columns have a specific value -

i have table looks this:

id    x1    x2    x3    x4 1     20    30    0     0 2     60    0     0     0 3     10    30    0     0 4     30    30    30    30 

i want able query , return id number of columns have more 0 value in row. result this:

id    count 1     2 2     1 3     2 4     4 

try this:

select id, z.cnt mytable cross apply (select count(*) cnt               (values (x1), (x2), (x3), (x4)) x(y)              x.y > 0) z 

this query makes use of table value constructor create in-line table rows columns of initial table. performing count on in-line table, can number of columns greater zero.

i think scales if have more 4 columns.

demo here