请教：该SQL怎么写？

[chq_top]

Tab1（date，unit，money），字段分别为日期，单位和累计投资；数据举例如下：
（2004-1-2,SC,1000）
(2004-3-10,SC,2000）
(2004-2-1,CQ,1300）
(2004-4-2,SC,5200）
(2004-4-2,CQ,2500）
.......
要求如下：
按年份、月统计出每个单位的投资累积值，显示为
SC CQ
2004-1 1000 0
2004-2 1000 1300
2004-3 2000 1300
2004-4 5200 2500
2004-5 5200 2500
。。。。。

请问，该SQL怎么写？

[chq_top]

单位个数不定，同时从1 到 12 月都要显示。

[playyuer]

select min(dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))
,sum(case when a.unit = 'SC' then a.[money] else 0 end) as sc
,sum(case when a.unit = 'CQ' then a.[money] else 0 end) as cq
from
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
) N

left join

(select min(date) as d
from tab1
group by datediff(year,0,date)) Y

on datediff(year,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)),Y.d)=0

left join

Tab1 a on datediff(month,a.date, dateadd(month,N.i,dateadd(year,datediff(year,0,d),0))) = 0

group by datediff(month,0,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))

[playyuer]

select cast(year(min(dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))) as varchar)
+ '-' + cast(month(min(dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))) as varchar)
,sum(case when a.unit = 'SC' then a.[money] else 0 end) as sc
,sum(case when a.unit = 'CQ' then a.[money] else 0 end) as cq
from
(
select 0 as i
union all
select 1
union all
select 2
union all
select 3
union all
select 4
union all
select 5
union all
select 6
union all
select 7
union all
select 8
union all
select 9
union all
select 10
union all
select 11
) N

left join

(select min(date) as d
from tab1
group by datediff(year,0,date)) Y

on datediff(year,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)),Y.d)=0

left join

Tab1 a on datediff(month,a.date, dateadd(month,N.i,dateadd(year,datediff(year,0,d),0))) = 0

group by datediff(month,0,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))


declare @ varchar(8000)
,@1 varchar(8000)
,@2 varchar(8000)
,@3 varchar(8000)
,@4 varchar(8000)
,@5 varchar(8000)

set @ = ''
set @1 = ''
set @2 = ''
set @3 = ''
set @4 = ''
set @5 = ''

select @ = @
+
',sum(case when a.unit = '''
+ unit + ''' then a.[money] else 0 end) as ' + unit
from Tab1
group by unit


select @1 = 'select cast(year(min(dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))) as varchar) + ''-'' + cast(month(min(dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))) as varchar) as 年月'
,@2 = ' from (select 0 as i union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9 union all select 10 union all select 11) N'
,@3 = ' left join (select min(date) as d from tab1 group by datediff(year,0,date)) Y on datediff(year,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)),Y.d)=0'
,@4 = ' left join Tab1 a on datediff(month,a.date, dateadd(month,N.i,dateadd(year,datediff(year,0,d),0))) = 0'
,@5 = ' group by datediff(month,0,dateadd(month,N.i,dateadd(year,datediff(year,0,Y.d),0)))'

exec (@1 + @ + @2 + @3 + @4 + @5)

[chq_top]

展现如下：
SC CQ
2004-1 1000 0
2004-2 1000 1300
2004-3 2000 1300
2004-4 5200 2500
2004-5 5200 2500
.......
2004-12 5200 2500

[playyuer]

select year(min(date)) as 年,month(min(date)) as 月
,(select sum([money])
from Tab1
where datediff(month,date,a.date) = 0 and unit = 'SC') as SC
,(select sum([money])
from Tab1
where datediff(month,date,a.date) = 0 and unit = 'CQ') as CQ
from Tab1 a
group by datediff(month,0,date)

[playyuer]

select year(min(date)) as 年,month(min(date)) as 月
,(select sum([money])
from Tab1
where datediff(month,date,a.date) = 0 and unit = 'SC') as SC
,(select sum([money])
from Tab1
where datediff(month,date,a.date) = 0 and unit = 'CQ') as CQ
from Tab1 a
group by datediff(month,0,date)


select year(min(date)) as 年,month(min(date)) as 月
,sum(case when unit = 'SC' then [money] else 0 end) as sc
,sum(case when unit = 'CQ' then [money] else 0 end) as cq
from Tab1
group by datediff(month,0,date)

[varysoft]

select cast(datepart(yy,date) as char(4))+'-'+cast(datepart(mm,date) as varchar(2)) as dt
,sum((case unit when 'sc' then money else 0 end)) as sc
,sum((case unit when 'cq' then money else 0 end)) as cq
from tab1
group by datepart(yy,date),datepart(mm,date)