根据日期汇总后再行转列

Question

有表A：Id  Date          Amount 
1    2010-10-09      50.00 
2    2010-10-05      30.00 
3    2010-10-23      40.00 
4    2010-11-29      250.00 
5    2009-11-06      43.00 
6    2009-12-06      69.00 
7    2009-12-23      31.00 
8    2008-04-26      33.00 
9    2008-03-11      59.00 其中查询条件有日期范围限制。例如我想查2009年11月份到2010年5月份的数据。根据年份和月份来汇总amount得出其总和,并且需要列转成行。例如上面的数据我想得到的结果2009-11  2009-12  2010-01  2010-02  2010-03 2010-04  2010-05
43.00     100.00     0       0        0        0        0
再如我的查询日期条件是：从2010年7月份到2011年1月份的数据
结果：
2010-07   2010-08  2010-09 2010-10  2010-11  2010-12  2011-01
0           0         0     120.00   250.00    0        0就是说根据日期的限定条件，行转列的时候也要根据这个日期范围

Answer 1

declare @sql varchar(max)
set @sql = 'select convert(varchar(7),date,120)date'
select @sql = @sql + ',max(case convert(varchar(7),date,120) when ''' + date + ''' then amount else 0 end)[' + date + ']'
from (select distinct convert(varchar(7),date,120) from tb where date between '2009-11-1' and '2010-5-1')u
select @sql = @sql + ' from tb group by convert(varchar(7),date,120)'
exec(@sql)

Answer 2

declare @sql varchar(max)
set @sql = 'select convert(varchar(7),date,120)date'
select @sql = @sql + ',max(case convert(varchar(7),date,120) when ''' + date + ''' then amount else 0 end)[' + date + ']'
from (select distinct convert(varchar(7),date,120)date from tb where date between '2009-11-1' and '2010-5-1')u
select @sql = @sql + ' from tb group by convert(varchar(7),date,120)'
exec(@sql)

Answer 3

declare @sql varchar(max)
set @sql = 'select convert(varchar(7),date,120)date'
select @sql = @sql + ',max(case convert(varchar(7),date,120) when ''' + date + ''' then amount else 0 end)[' + date + ']'
from (select distinct convert(varchar(7),date,120)date from tb where date between '2009-11-1' and '2010-5-1')u
select @sql = @sql + ' from tb group by convert(varchar(7),date,120) where date between ''2009-11-1'' and ''2010-5-1'''
exec(@sql)--这个试试吧！没测试！ 

Answer 4

code:
declare @sql varchar(8000)
set @sql='select '
select @sql=@sql+ 'sum(case date when '''+date+''' then Amount 
else 0 end) as '''+date+''','from (select distinct date from A) as a
select @sql=left(@sql,len(@sql)-1)+'from a '
exec(@sql)

Answer 5

use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#1') is null
drop table #1
Go
Create table #1([Id] int,[Date] Datetime,[Amount] decimal(18,2))
Insert #1
select 1,'2010-10-09',50.00 union all
select 2,'2010-10-05',30.00 union all
select 3,'2010-10-23',40.00 union all
select 4,'2010-11-29',250.00 union all
select 5,'2009-11-06',43.00 union all
select 6,'2009-12-06',69.00 union all
select 7,'2009-12-23',31.00 union all
select 8,'2008-04-26',33.00 union all
select 9,'2008-03-11',59.00
Go--2009年11月份到2010年5月份
declare @StartDate datetime,@EndDate datetime
select @StartDate='20091101',@EndDate='20100501'--以上為傳參declare @s nvarchar(4000),@Dt datetime
select @Dt=@StartDatewhile @Dt<@EndDate
select @s=isnull(@s+',',' select ')+'['+convert(varchar(7),@Dt,120)+']=sum(case when convert(varchar(7),[Date],120)='''+convert(varchar(7),@Dt,120)+''' then [Amount] else 0 end)',@Dt=dateadd(m,1,@Dt)
set @s= @s+' from #1 where convert(varchar(7),[Date],120) between '''+convert(varchar(7),@StartDate,120)+''' and '''+convert(varchar(7),@EndDate,120)+''''
exec(@s)
/*
2009-11 2009-12 2010-01 2010-02 2010-03 2010-04
43.00 100.00 0.00 0.00 0.00 0.00
*/

Answer 6

用Reporting Services里面的矩阵就可以轻松实现。

Answer 7

alter PROCEDURE [dbo].[TestA]
  @startDate  as datetime,
  @endDate as datetime
AS
SET NOCOUNT ON    declare @startMonth nvarchar(8)
  declare @endMonth nvarchar(8)
  declare @i int
  declare @Sql nvarchar(4000)
  set @startMonth=case when @startDate='' then '' else convert(nvarchar(7),@startDate,120) end
  set @endMonth=case when @endDate='' then '' else convert(nvarchar(7),@endDate,120) endif @startMonth<>'' and  @endMonth<>'' and @startMonth>@endMonth 
begin
select '开始时间不能大于结束时间'
return
endset @sql='select '
begin
with TT as 
(select convert(nvarchar(7),date,120) as date,sum(Amount) as Amount from #tableA　--你的表A
 where (@startMonth='' or @startMonth<>'' and convert(nvarchar(7),date,120)>=@startMonth)
 and (@endMonth='' or @endMonth<>'' and convert(nvarchar(7),date,120)<=@endMonth)
 group by convert(nvarchar(7),date,120))
select @sql=@sql+cast(cast(Amount as decimal(10,2)) as nvarchar(10)) +' as '''+date+''', ' from TT
end
set @sql=substring(@sql,1,len(@sql)-1)
print @sql
exec(@sql)SET NOCOUNT OFF
调用使传开始时间和结束时间就OK了。。
如： exec TestA '2008-1-1', '2010-9-9'

Answer 8

奥。。不好意思把上边的alter PROCEDURE [dbo].[TestA]改成create PROCEDURE [dbo].[TestA]