有下面这样一组数据（示例）：
Year Name
1999 A
1999 A
1999 A
1999 B
1999 B
1999 B
1999 C
1999 C
1999 D
2000 A
2000 B
2000 C
2000 D
2000 D
2000 D
2001 A
2001 A
2001 B
2001 B
2001 C
2001 C
2001 D
2002 A
2002 A
2002 A
2002 B
2002 C
2002 C
2002 C
需要在SQL里查询得出如下形式的结果，请教可以使用什么方法？
A B C D
1999 （个数） （个数） （个数） （个数）
2000 （个数） （个数） （个数） （个数）
2001 （个数） （个数） （个数） （个数）
2002 （个数） （个数） （个数） （个数）
------解决方案--------------------
- SQL code
if object_id('[TB]') is not null drop table [TB]gocreate table [TB] (Year int,Name nvarchar(2))insert into [TB]select 1999,'A' union allselect 1999,'A' union allselect 1999,'A' union allselect 1999,'B' union allselect 1999,'B' union allselect 1999,'B' union allselect 1999,'C' union allselect 1999,'C' union allselect 1999,'D' union allselect 2000,'A' union allselect 2000,'B' union allselect 2000,'C' union allselect 2000,'D' union allselect 2000,'D' union allselect 2000,'D' union allselect 2001,'A' union allselect 2001,'A' union allselect 2001,'B' union allselect 2001,'B' union allselect 2001,'C' union allselect 2001,'C' union allselect 2001,'D' union allselect 2002,'A' union allselect 2002,'A' union allselect 2002,'A' union allselect 2002,'B' union allselect 2002,'C' union allselect 2002,'C' union allselect 2002,'C'select * from [TB]SELECT YEAR,SUM(CASE WHEN NAME ='a' THEN 1 ELSE 0 END ) AS 'A',SUM(CASE WHEN NAME ='b' THEN 1 ELSE 0 END ) AS 'B',SUM(CASE WHEN NAME ='c' THEN 1 ELSE 0 END ) AS 'C',SUM(CASE WHEN NAME ='d' THEN 1 ELSE 0 END ) AS 'D'FROM TBGROUP BY YEAR/*YEAR A B C D----------- ----------- ----------- ----------- -----------1999 3 3 2 12000 1 1 1 32001 2 2 2 12002 3 1 3 0(4 行受影响)*/
------解决方案--------------------
- SQL code
Slect year, sum(case when name='A' then 1 else 0 end) as [A], sum(case when name='B' then 1 else 0 end) as [B], sum(case when name='C' then 1 else 0 end) as [C], sum(case when name='D' then 1 else 0 end) as [D]From Table group by yearorder by year