有些类似行转列的统计

有点类似行转列的统计
数据格式如下：
cid totaldate
1 102011-12
1 34 2012-04
2 152011-10
2 452011-12
2 142012-05
3 252011-11
4 642011-10
4 252011-12
4 342012-03

我要查询从2011-10到2012-05这个时间段的各CID的total是多少，结果如下：

cid 2011-10 2011-11 2011-12 2012-01 2012-02 2012-03 2012-04 2012-05
1 0 0 10 0 0 0 34 0
2 15 0 45 0 0 0 0 14
3 0 25 10 0 0 0 0 0
4 64 0 25 0 0 34 0 0

这样的统计该如何写？
SQL2008

[解决办法]

SQL code

create table ing(cid int, total int, datem varchar(10))insert into ingselect 1, 10, '2011-12' union allselect 1, 34, '2012-04' union allselect 2, 15, '2011-10' union allselect 2, 45, '2011-12' union allselect 2, 14, '2012-05' union allselect 3, 25, '2011-11' union allselect 4, 64, '2011-10' union allselect 4, 25, '2011-12' union allselect 4, 34, '2012-03'declare @startdate varchar(8),        @enddate varchar(8),        @sql varchar(6000)select @startdate='2011-10', -- 开始时间       @enddate='2012-05',   -- 结束时间       @sql='select cid,'select @sql=@sql+'max(case when datem='''+datem+''' then total else 0 end) '''+datem+''', 'from (select distinct datem from ing where cast(datem+'-01' as date)      between @startdate+'-01' and @enddate+'-01') torder by datemselect @sql=left(@sql,len(@sql)-1)+' from ing  where cast(datem+''-01'' as date)   between '''+@startdate+'-01'' and '''+@enddate+'-01''  group by cid'  exec(@sql)  /*cid         2011-10     2011-11     2011-12     2012-03     2012-04     2012-05----------- ----------- ----------- ----------- ----------- ----------- -----------1           0           0           10          0           34          02           15          0           45          0           0           143           0           25          0           0           0           04           64          0           25          34          0           0(4 row(s) affected)*/
[解决办法]
pivot方案
SQL code
if OBJECT_ID('tempdb..#test') is not null    drop table #testcreate table #test (cid int, total int, datem varchar(10))insert into #testselect 1, 10, '2011-12' union allselect 1, 34, '2012-04' union allselect 2, 15, '2011-10' union allselect 2, 45, '2011-12' union allselect 2, 14, '2012-05' union allselect 3, 25, '2011-11' union allselect 4, 64, '2011-10' union allselect 4, 25, '2011-12' union allselect 4, 34, '2012-03'declare @mthList varchar(max)select @mthList=coalesce(@mthList + ',','')+'['+datem+']' from (select distinct datem from #test where datem between '2011-10' and '2012-05')aset @mthList='select * from #testpivot     (sum(total) for datem in (' + @mthList +')) b'print @mthListexec(@mthList)cid    2011-10    2011-11    2011-12    2012-03    2012-04    2012-051    NULL    NULL    10    NULL    34    NULL2    15    NULL    45    NULL    NULL    143    NULL    25    NULL    NULL    NULL    NULL4    64    NULL    25    34    NULL    NULL