求一汇总语句（sql2008）
设有表格如下：

A——B
a——0
a——0
a——1
b——0
b——1
b——1
b——1

要求得到：
A——B0——B1
a——2 ——1
b——1 ——3

即：汇总出列［A］各种值分别出现列B=0和列B=1的次数。
如果不使用子查询，能否实现？
比如:
select [A],... from TABLE Group by [A]

谢谢！


[解决办法]

SQL code

select A , (select count(1) from test where B=0 and t.A = test.A) as B0 , (select count(1) from test where B=1 and t.A = test.A) as B1 from test t group by A
[解决办法]
SQL code
declare @t table ( A char(1),B tinyint)insert into @tselect 'a',0 union allselect 'a',0 union allselect 'a',1 union allselect 'b',0 union allselect 'b',1 union allselect 'b',1 union allselect 'b',1 select A,SUM(case when B = 0 THEN 1 ELSE 0 END ) AS B0,         SUM(case when B = 1 THEN 1 ELSE 0 END ) AS B1from @t group by A-----------------------------------------(7 行受影响)A    B0          B1---- ----------- -----------a    2           1b    1           3(2 行受影响)