select col,count(*) as cnt
from(
select substring(a.col,b.number,charindex(',',a.col+',',b.number)-b.number) col
from t1 a,master..spt_values b
where b.type = 'p' and b.number between 1 and len(a.col)
    and len(a.col) >= 1 and substring(','+a.col,b.number,1) = ','
) t
group by col

------解决方案--------------------
SQL code

declare @t1 table (content varchar(10))
insert into @t1
select 'a,b,c,d' union all
select 'b,d' union all
select 'b,c,d'

select c1,count(1) as cnt from @t1 a 
left join 
(select 'a' as c1 union select 'b' union select 'c' union select 'd') b 
on charindex(','+b.c1+',',','+a.content+',')>0 group by c1
/*
c1   cnt
---- -----------
a    1
b    3
c    2
d    3
*/