if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (Year int,Name nvarchar(2))
insert into [TB]
select 1999,'A' union all
select 1999,'A' union all
select 1999,'A' union all
select 1999,'B' union all
select 1999,'B' union all
select 1999,'B' union all
select 1999,'C' union all
select 1999,'C' union all
select 1999,'D' union all
select 2000,'A' union all
select 2000,'B' union all
select 2000,'C' union all
select 2000,'D' union all
select 2000,'D' union all
select 2000,'D' union all
select 2001,'A' union all
select 2001,'A' union all
select 2001,'B' union all
select 2001,'B' union all
select 2001,'C' union all
select 2001,'C' union all
select 2001,'D' union all
select 2002,'A' union all
select 2002,'A' union all
select 2002,'A' union all
select 2002,'B' union all
select 2002,'C' union all
select 2002,'C' union all
select 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 TB
GROUP BY YEAR

/*
YEAR        A           B           C           D
----------- ----------- ----------- ----------- -----------
1999        3           3           2           1
2000        1           1           1           3
2001        2           2           2           1
2002        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 year
order by year