SQL Server统计Table里值不为0的列数

[x287634334]

各位大神，咨询一个问题：
假如我这个表里有字段a1，a2，a3， a4，a5，a6这几个字段，其值分别是1，0，0，5，0，0
如本例，值不为0的列数是2。
那么如何统计出这个表里值不等于0的列数？

[卖水果的net]

create table test(a1 int,a2 int,a3 int,a4 int,a5 int,a6 int)
go
insert into test values(1,0,0,5,0,0)
go
select  case when a1 !=0 then 1 else 0 end + 
        case when a2 !=0 then 1 else 0 end + 
        case when a3 !=0 then 1 else 0 end + 
        case when a4 !=0 then 1 else 0 end + 
        case when a5 !=0 then 1 else 0 end + 
        case when a6 !=0 then 1 else 0 end 
from test 
go
drop table test 
go


(1 行受影响)

-----------
2

(1 行受影响)

[中国风]

SELECT *, SIGN(a1)+SIGN(a2)+SIGN(a3)+SIGN(a4)+SIGN(a5)+SIGN(a6) AS [>0]
FROM tableName