有点类似行转列的统计

Question

数据格式如下：
cid  total date
1  10 2011-12
1        34     2012-04
2  15 2011-10
2  45 2011-12
2  14 2012-05
3  25 2011-11
4  64 2011-10
4  25 2011-12
4  34 2012-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

Answer 1

create table ing
(cid int, total int, datem varchar(10))insert into ing
select 1, 10, '2011-12' union all
select 1, 34, '2012-04' union all
select 2, 15, '2011-10' union all
select 2, 45, '2011-12' union all
select 2, 14, '2012-05' union all
select 3, 25, '2011-11' union all
select 4, 64, '2011-10' union all
select 4, 25, '2011-12' union all
select 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') t
order 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          0
2           15          0           45          0           0           14
3           0           25          0           0           0           0
4           64          0           25          34          0           0(4 row(s) affected)
*/

Answer 2

行列转换，唐诗正解！pviot也可以把

Answer 3

pivot方案if OBJECT_ID('tempdb..#test') is not null
drop table #test
create table #test (cid int, total int, datem varchar(10))insert into #test
select 1, 10, '2011-12' union all
select 1, 34, '2012-04' union all
select 2, 15, '2011-10' union all
select 2, 45, '2011-12' union all
select 2, 14, '2012-05' union all
select 3, 25, '2011-11' union all
select 4, 64, '2011-10' union all
select 4, 25, '2011-12' union all
select 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 #test
pivot 
(sum(total) for datem in (' + @mthList +')) b'
print @mthList
exec(@mthList)cid 2011-10 2011-11 2011-12 2012-03 2012-04 2012-05
1 NULL NULL 10 NULL 34 NULL
2 15 NULL 45 NULL NULL 14
3 NULL 25 NULL NULL NULL NULL
4 64 NULL 25 34 NULL NULL