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.
Comments
Post a Comment