-- 建测试表
create table piao
([A] int, [B] varchar(4), [C] varchar(4))

-- 列[B]含a,b,c,d,e
insert into piao
select 1, 'a', 'tt' union all
select 1, 'a', 'tt' union all
select 1, 'a', 'tt' union all
select 1, 'b', 'tt' union all
select 1, 'd', 'tt' union all
select 2, 'a', 'tt' union all
select 2, 'a', 'tt' union all
select 2, 'b', 'tt' union all
select 2, 'b', 'tt' union all
select 2, 'c', 'tt' union all
select 2, 'd', 'tt' union all
select 2, 'e', 'tt'


-- 动态SQL统计个数 
declare @sql varchar(6000)

select @sql='
select A,'+
stuff((select distinct ',isnull([B_'+B+'],0) B_'+B from piao for xml path('')),1,1,'')
+' from (select A,''B_''+B B,count(1) ct from piao group by A,B) t
pivot(max(ct) for B in('+ 
stuff((select distinct ',[B_'+B+']' from piao for xml path('')),1,1,'')+')) p '

exec(@sql)

-- 结果
/*
A           B_a         B_b         B_c         B_d         B_e
----------- ----------- ----------- ----------- ----------- -----------
1           3           1           0           1           0
2           2           2           1           1           1

(2 row(s) affected)
*/