求一条SQL语句

[月之点点]

表中数据

分组(fz) 状态(zt) 标题(bt)
A组 1 XXXXX
B组 1 XXXXX
C组 2 XXXXX
A组 2 XXXXX
A组 3 XXXXX
C组 2 XXXXX
A组 1 XXXXX
C组 3 XXXXX
B组 1 XXXXX


----------------------------------------------
要求查处：

分组 状态1的条数 状态2的条数 状态3的条数
A组 2 1 1
B组 2 0 0
C组 0 2 1


求SQL语句如何写？ 表中的数据量大约10W条数据！ 跪求~~~

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-10-08 15:35:05
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) 
--	Jun 10 2013 20:09:10 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go 
create table [huang]([分组] varchar(3),[状态] int,[标题] varchar(5))
insert [huang]
select 'A组',1,'XXXXX' union all
select 'B组',1,'XXXXX' union all
select 'C组',2,'XXXXX' union all
select 'A组',2,'XXXXX' union all
select 'A组',3,'XXXXX' union all
select 'C组',2,'XXXXX' union all
select 'A组',1,'XXXXX' union all
select 'C组',3,'XXXXX' union all
select 'B组',1,'XXXXX'
--------------开始查询--------------------------


declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename('状态'+CONVERT(VARCHAR(2),[状态])+'的条数')+'=sum(case when [状态]='+quotename([状态],'''')+' then 1 else 0 end)'
from [huang] group by [状态]
exec('select [分组]'+@s+' from [huang] group by [分组]')
----------------结果----------------------------
/* 
分组   状态1的条数      状态2的条数      状态3的条数
---- ----------- ----------- -----------
A组   2           1           1
B组   2           0           0
C组   0           2           1
*/

[t101lian]

select [分组], sum(case when [状态]=1 then 1 else 0 end  ) as 状态1 ,
              sum(case when [状态]=2 then 1 else 0 end  ) as 状态2,
              sum(case when [状态]=3 then 1 else 0 end  ) as 状态3 
from #dba group by [分组]

[阳泉酒家小当家]

if object_id('[huang]') is not null drop table [huang]
go 

create table [huang]([分组] varchar(3),[状态] int,[标题] varchar(5))
insert [huang]
select 'A组',1,'XXXXX' union all
select 'B组',1,'XXXXX' union all
select 'C组',2,'XXXXX' union all
select 'A组',2,'XXXXX' union all
select 'A组',3,'XXXXX' union all
select 'C组',2,'XXXXX' union all
select 'A组',1,'XXXXX' union all
select 'C组',3,'XXXXX' union all
select 'B组',1,'XXXXX'
--------------开始查询--------------------------


--方法1：静态语句,如果还有其他的状态，往上加就行
select [分组],

       COUNT(case [状态] when 1 then 1 else null end) as [状态1的条数],
       COUNT(case [状态] when 2 then 1 else null end) as [状态2的条数],
       COUNT(case [状态] when 3 then 1 else null end) as [状态3的条数]
              
from [huang] h
group by [分组]


--方法2：这种方法更加简洁，就是列名不是[状态1的条数],而就是1
select *
from
(
	select  [分组],
			[状态]
	from [huang] 
)h
pivot
(
	count([状态]) 
    for [状态] in ([1] ,[2] ,[3]) --此处不能写成：[1] as [状态1的条数]
) hh