CREATE TABLE t(id INT, a INT, b INT, c INT);

​

INSERT INTO t

SELECT 10001,1,3,3 UNION

SELECT 10002,0,0,0 UNION

SELECT 10003,3,6,0 UNION

SELECT 10004,7,0,0 UNION

SELECT 10005,0,0,0;

​

WITH cte AS (SELECT ID

               ,CAST(IIF(a!=0,1,NULL) AS VARCHAR(10)) a

               ,CAST(IIF(b!=0,1,NULL) AS VARCHAR(10)) b

               ,CAST(IIF(c!=0,1,NULL) AS VARCHAR(10)) c 

            FROM t)

SELECT combination, [count]

FROM (SELECT  a=COUNT(a), b=COUNT(b), c=COUNT(c), ab=COUNT(a+b), ac=COUNT(a+c), bc=COUNT(b+c), abc=COUNT(a+b+c)

      FROM cte) s

UNPIVOT ([count] FOR combination IN (a,b,c,ab,ac,bc,abc))AS unpvt

​

​

​