小弟我有一个SQL语句有关问题？查询的

我有一个SQL语句问题？查询的
如下表结果 

id name status
1 xxxx 1
2 yyyy 2
3 zzzz 3
4 ffff 1
5 erer 1
6 gffg 3
7 xdfd 1
8 fdvd 1
9 ffff 2
10 rttr 1

我想要这样的结果 

Sum S1 S2 S3
10 6 2 2


[解决办法]
select a.k1, b.k2,c.k3
from 
(select count(id) as k1 from test where TS = "1") a,
(select count(id) as k2 from test where TS ="2") b，
(select count(id) as k3 from test where TS ="2") c;

个人感觉这样的写法很不好。不知道有没有更优雅的办法。
[解决办法]
写了半小时，没有写出来，要自动判断status数量，有要sum ，简单的结果，不会写
顶一下
[解决办法]
方法1，固定S1,S2,S3个数

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(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 s3from #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)tpexec('select count(1) as ''总数'','+@sql+' from #t')