declare @t table (ID INT IDENTITY(1,1),a int,b int,c int,d int)
insert into @t
values(1,0,0,0),(0,1,0,0),(1,2,0,0),(1,2,3,4),(1,2,3,0)
;with cte as
(
select * from @t
unpivot
(colvalue for colname in(a,b,c,d)) unpvt
)
select t.*,cnt from @t t
INNER JOIN (
select ID,COUNT(*) as cnt
from cte
WHERE colvalue > 0
GROUP BY ID
) x ON x.ID = t.ID
Output :
