求一个SQL语句。 - MS-SQL Server / 应用实例

假设SQL数据表如不：
id field A field B ...
1 AA S1
2 AA S2
3 AA S3
4 AA S2  
5 B S2
6 C S3
7 D S1
8 D S1
9 D S2
10 D S2
其中FIELD B的取值范围是有限的，要求得到以下结果：

FIELD A S1 S2 S3（上表只有三种取值）
AA 1 2 1
B 1
C 1
D 2 2
   
请问SQL 语句该如何写？

---

SQL code:
--既然fieldB有限那就可以写死
select FieldA,
S1=sum(case when FieldB='S1' then 1 else 0 end),
S2=sum(case when FieldB='S2' then 1 else 0 end),
S3=sum(case when FieldB='S3' then 1 else 0 end)
from [Table]
group by FieldA

---

SQL code:
select FieldA,
S1=sum(case when FieldB='S1' then 1 else 0 end),
S2=sum(case when FieldB='S2' then 1 else 0 end),
S3=sum(case when FieldB='S3' then 1 else 0 end)
from [Table]
group by FieldA

---

传说中的行列转换
精华帖
很经典

---

SQL code

---

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.c