SELECT 统计 写法，

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 @[email protected]+',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 @[email protected]+',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 testgocreate table test(NAME int, CODE varchar(2))goinsert into testselect 10103, 'A1' union allselect 10103, 'A2' union allselect 10104, 'A1' union allselect 10105, 'B5' union allselect 10103, 'A1'godeclare @sql varchar(4000)select @sql=isnull(@sql+',','')+' sum(case when CODE='''+CODE+''' then t.cnt else 0 end) '+CODEfrom (select distinct CODE from test) tset @sql='select NAME,[email protected]+' 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           010104       1           0           010105       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     tGROUP BY name--动态DECLARE @sql VARCHAR(max)SELECT  @sql = Isnull(@sql + ',','') + ' sum(case when CODE=''' + code + ''' then t.cnt else 0 end) ' + codeFROM   (SELECT DISTINCT code        FROM   test) tSET @sql = 'select NAME,' + @sql + ' from (select NAME,CODE,count(1) cnt from test group by NAME,CODE) t group by t.NAME'EXEC( @sql)