求一条SQL语句。

Question

有一个表test，如下：
分类        责任人
A1            王
A2            王
A2            钱
B1            汪
B2            陈
B3            周
C             江
D             胡
E             李
F1            刘
F2            赵
F3            孙
F4            温
现在要做一个统计表，格式如下：责任人     A类（含A1,A2）    B类(B1,B2,B3)      C类      D类      E类     F类（含F1,F2,F3,F4）   小计    
王         2次                0次               0次       0次     0次         0次                 2次
钱         1次                0次               0次       0次     0次         0次                 1次
汪         0次                1次               0次       0次     0次         0次                 1次
陈         0次                1次               0次       0次     0次         0次                 1次
周         0次                1次               0次       0次     0次         0次                 1次
江         0次                0次               1次       0次     0次         0次                 1次
胡         0次                0次               0次       1次     0次         0次                 1次
李         0次                0次               0次       0次     1次         0次                 1次
刘         0次                0次               0次       0次     0次         1次                 1次
赵         0次                0次               0次       0次     0次         1次                 1次
孙         0次                0次               0次       0次     0次         1次                 1次
温         0次                0次               0次       0次     0次         1次                 1次
求这样的SQL语句怎么写？谢谢大侠！

Answer 1

if object_id('[test]') is not null drop table [test]
go
create table [test]([分类] varchar(2),[责任人] varchar(2))
insert [test]
select 'A1','王' union all
select 'A2','王' union all
select 'A2','钱' union all
select 'B1','汪' union all
select 'B2','陈' union all
select 'B3','周' union all
select 'C','江' union all
select 'D','胡' union all
select 'E','李' union all
select 'F1','刘' union all
select 'F2','赵' union all
select 'F3','孙' union all
select 'F4','温'
go-->数据查询：
select 
  责任人,
  A类=sum(case when left(分类,1)='A' then 1 else 0 end),
  B类=sum(case when left(分类,1)='B' then 1 else 0 end),
  C类=sum(case when left(分类,1)='C' then 1 else 0 end),
  D类=sum(case when left(分类,1)='D' then 1 else 0 end),
  E类=sum(case when left(分类,1)='E' then 1 else 0 end),
  F类=sum(case when left(分类,1)='F' then 1 else 0 end),
  小计=count(*)
from 
  test
group by
  责任人/**
责任人  A类          B类          C类          D类          E类          F类          小计
---- ----------- ----------- ----------- ----------- ----------- ----------- -----------
陈    0           1           0           0           0           0           1
胡    0           0           0           1           0           0           1
江    0           0           1           0           0           0           1
李    0           0           0           0           1           0           1
刘    0           0           0           0           0           1           1
钱    1           0           0           0           0           0           1
孙    0           0           0           0           0           1           1
汪    0           1           0           0           0           0           1
王    2           0           0           0           0           0           2
温    0           0           0           0           0           1           1
赵    0           0           0           0           0           1           1
周    0           1           0           0           0           0           1(12 行受影响)
**/