select name,sum(case when code ='A1' then 1 else 0 end ) as 'A1',sum(case when code ='A2' then 1 else 0 end ) as 'A2',sum(case when code ='B5' then 1 else 0 end ) as 'B5'  from T  group by name

------解决方案--------------------
declare @s varchar(4000)
set @s=''
select @s=@s+',sum(case when code ='''+Code+''' then 1 else 0 end ) as '+Code+''
from (select distinct Code from 现有表 order by Code) t
set @s='select name,' + @s + ' from 现有表 group by name'
EXECUTE (@s)

------解决方案--------------------
declare @s varchar(4000)
set @s=''
select @s=@s+',sum(case when code ='''+Code+''' then 1 else 0 end ) as '+Code+''
from (select distinct Code from 现有表 order by Code) t
set @s='select name,' + @s + ' from 现有表 group by name'
EXECUTE (@s)

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

if object_id('test') is not null drop table test
go
create table test(NAME int, CODE varchar(2))
go
insert into test
select 10103, 'A1' union all
select 10103, 'A2' union all
select 10104, 'A1' union all
select 10105, 'B5' union all
select 10103, 'A1'
go
declare @sql varchar(4000)
select @sql=isnull(@sql+',','')+' sum(case when CODE='''+CODE+''' then t.cnt else 0 end) '+CODE
from (select distinct CODE from test) t
set @sql='select NAME,'+@sql+' from (select NAME,CODE,count(1) cnt from test group by NAME,CODE) t group by t.NAME'
exec(@sql)
/*
NAME        A1          A2          B5
----------- ----------- ----------- -----------
10103       2           1           0
10104       1           0           0
10105       0           0           1
*/

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

-- 静态
SELECT   name,
         Sum(CASE 
               WHEN code = 'A1' THEN 1
               ELSE 0
             END) AS 'A1',
         Sum(CASE 
               WHEN code = 'A2' THEN 1
               ELSE 0
             END) AS 'A2',
         Sum(CASE 
               WHEN code = 'B5' THEN 1
               ELSE 0
             END) AS 'B5'
FROM     t
GROUP BY name

--动态
DECLARE @sql VARCHAR(max)
SELECT  @sql = Isnull(@sql + ',','') + ' sum(case when CODE=''' + code + ''' then t.cnt else 0 end) ' + code
FROM   (SELECT DISTINCT code
        FROM   test) t

SET @sql = 'select NAME,' + @sql + ' from (select NAME,CODE,count(1) cnt from test group by NAME,CODE) t group by t.NAME'

EXEC( @sql)