select [2001]=sum(case when year(time)='2001' then 1 else 0 end),
[2002]=sum(case when year(time)='2002' then 1 else 0 end),
[2003]=sum(case when year(time)='2003' then 1 else 0 end),
[2004]=sum(case when year(time)='2004' then 1 else 0 end)
....
from 表
[2002]=sum(case when year(time)='2002' then 1 else 0 end),
[2003]=sum(case when year(time)='2003' then 1 else 0 end),
[2004]=sum(case when year(time)='2004' then 1 else 0 end)
....
from 表
FROM Table
GROUP BY YEAR(datetime)
结果:
A1 A2
2 2001
1 2002
1 2003其实一样的,只不过竖着列而已.
time datetime)insert into test
select
1 , '2001-1-1'
union select
2 , '2001-2-6'
union select
3 , '2002-2-5'
union select
4 , '2003-5-9'
select
sum(case when datepart(yy,time)=2001 then 1 else 0 end) [2001],
sum(case when datepart(yy,time)=2002 then 1 else 0 end) [2002],
sum(case when datepart(yy,time)=2003 then 1 else 0 end) [2003],
sum(case when datepart(yy,time)=2004 then 1 else 0 end) [2004]
from testdrop table test
time datetime)insert into test
select
1 , '2001-1-1'
union select
2 , '2001-2-6'
union select
3 , '2002-2-5'
union select
4 , '2003-5-9'
DECLARE @SQL VARCHAR(8000)
SET @SQL='SELECT -1'
SELECT @SQL= @SQL+ ',sum(CASE WHEN datepart(yy,time) = ''' + tt + ''' THEN 1 else 0 END) [' +tt+ ']' FROM (SELECT DISTINCT cast(datepart(yy,time) as char(4))as tt FROM test) A
SET @SQL=@SQL+' FROM test ' exec (@SQL) drop table test