【求助】SQL的统计

[blackwhole]

表的内容为：
a b
=========
1 a
1 b
2 a
2 a
2 c
3 a
3 b
3 b
3 b
3 c
3 c

我想统计成这样：
1 a=1, b=1, c=0
2 a=2, b=0, c=1
3 a=1, b=2, c=2

请问用一条SQL语句可以完成么？
如果可以，该怎么样写呢？

苦恼很多天了。现在用了多个sql语句分别统计，然后再更新到一个表里的。

[dobear_0922]

--> By dobear_0922(小熊) 2008-11-06 17:30:13
--> 测试数据：@t
declare @t table([a] int,[b] varchar(1))
insert @t
select 1,'a' union all
select 1,'b' union all
select 2,'a' union all
select 2,'a' union all
select 2,'c' union all
select 3,'a' union all
select 3,'b' union all
select 3,'b' union all
select 3,'b' union all
select 3,'c' union all
select 3,'c'

--select * from @t

select a, info='a='+ rtrim(sum(case b when 'a' then 1 else 0 end))
	+ ', b=' + rtrim(sum(case b when 'b' then 1 else 0 end))
	+ ', c=' + rtrim(sum(case b when 'c' then 1 else 0 end))
from @t 
group by a
/*
a           info
----------- ----------------------------------------------
1           a=1, b=1, c=0
2           a=2, b=0, c=1
3           a=1, b=3, c=2

(3 行受影响)
*/

[等不到来世]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[name] varchar(1))
insert [tb]
select 1,'a' union all
select 1,'b' union all
select 2,'a' union all
select 2,'a' union all
select 2,'c' union all
select 3,'a' union all
select 3,'b' union all
select 3,'b' union all
select 3,'b' union all
select 3,'c' union all
select 3,'c'

select * from [tb]

select	id
		,a=(select count(*) from tb where id=a.id and name='a')
		,b=(select count(*) from tb where id=a.id and name='b')
		,c=(select count(*) from tb where id=a.id and name='c')
from tb a
group by id

--测试结果:
/*
id          a           b           c
----------- ----------- ----------- -----------
1           1           1           0
2           2           0           1
3           1           3           2

(3 row(s) affected)

*/

[等不到来世]

顶do熊，哈哈！

[tiyuzhongxin789]

--> By dobear_0922(小熊) 2008-11-06 17:30:13
--> 测试数据：@t
declare @t table([a] int,[b] varchar(1))
insert @t
select 1,'a' union all
select 1,'b' union all
select 2,'a' union all
select 2,'a' union all
select 2,'c' union all
select 3,'a' union all
select 3,'b' union all
select 3,'b' union all
select 3,'b' union all
select 3,'c' union all
select 3,'c'

--select * from @t

select a, info='a='+ rtrim(sum(case b when 'a' then 1 else 0 end))
+ ', b=' + rtrim(sum(case b when 'b' then 1 else 0 end))
+ ', c=' + rtrim(sum(case b when 'c' then 1 else 0 end))
from @t
group by a
/*
a info
----------- ----------------------------------------------
1 a=1, b=1, c=0
2 a=2, b=0, c=1
3 a=1, b=3, c=2

(3 行受影响)
*/

[中国风]

--> --> (Roy)生成測試數據
 
if not object_id('Tempdb..#T') is null
	drop table #T
Go
Create table #T([a] int,[b] nvarchar(1))
Insert #T
select 1,N'a' union all
select 1,N'b' union all
select 2,N'a' union all
select 2,N'a' union all
select 2,N'c' union all
select 3,N'a' union all
select 3,N'b' union all
select 3,N'b' union all
select 3,N'b' union all
select 3,N'c' union all
select 3,N'c'
Go
declare @s nvarchar(4000)
set @s=''
select @s=@s+'+'','+[b]+'=''+rtrim(sum(case when [b]='+quotename([b],'''')+' then 1 else 0 end))' from #T group by [b]
set @s=stuff(stuff(@s,charindex(',',@s),1,''),1,1,'select [a],COl=')
exec(@s+' from #T group by [a]')


a           COl
----------- --------------------------------------------
1           a=1,b=1,c=0
2           a=2,b=0,c=1
3           a=1,b=3,c=2

[bingdian37]

学习

[blackwhole]

谢谢大家。
可以结贴了。

[kimi2008004]

HAOTIE