create table #t (id int identity,name varchar(10),status int)

insert into #t(name,status)values('xxxx',1)
insert into #t(name,status)values('yyyy',2)
insert into #t(name,status)values('zzzz',3)
insert into #t(name,status)values('ffff',1)
insert into #t(name,status)values('erer',1)
insert into #t(name,status)values('gffg',3)
insert into #t(name,status)values('xdfd',1)
insert into #t(name,status)values('fdvd',1)
insert into #t(name,status)values('ffff',2)
insert into #t(name,status)values('rttr',1)

select count(id) as 'SUM'
, sum(case status when 1 then 1 else 0 end) as 'S1'
, sum(case status when 2 then 1 else 0 end) as 'S2'
, sum(case status when 3 then 1 else 0 end) as 'S3'
from #t

------解决方案--------------------
探讨

方法2 更标准的做法 


SQL codedeclare @sql nvarchar(4000)
select @sql='select count(id) as ''SUM'''
select @sql = @sql+
', sum(case status when '''+ Convert(varchar(2), status) +''' then 1 else 0 end) [S'+ Convert(varchar(2), status) +']'
from (select distinct status from #t) a
select @sql = @sql + ' from #t'
exec sp_executesql @sql


------解决方案--------------------
SQL code
create table #t (id int identity,name varchar(10),status int)

insert into #t(name,status)values('xxxx',1)
insert into #t(name,status)values('yyyy',2)
insert into #t(name,status)values('zzzz',3)
insert into #t(name,status)values('ffff',1)
insert into #t(name,status)values('erer',1)
insert into #t(name,status)values('gffg',3)
insert into #t(name,status)values('xdfd',1)
insert into #t(name,status)values('fdvd',1)
insert into #t(name,status)values('ffff',2)
insert into #t(name,status)values('rttr',1)
--静态
select count(1) as '总数',
sum(case when status=1 then 1 else 0 end) as s1,
sum(case when status=2 then 1 else 0 end) as s2,
sum(case when status=3 then 1 else 0 end) as s3
from #t
--动态
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'sum(case when status='''+ltrim(status)+''' then 1 else 0 end) as [s'+ltrim(status)+']'
from (select distinct status from #t)tp
exec('select count(1) as ''总数'','+@sql+' from #t')