根据日期汇总后再行转列

[missukiss02]

有表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

就是说根据日期的限定条件，行转列的时候也要根据这个日期范围

[AcHerat]

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)

[AcHerat]

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)

[AcHerat]

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)

--这个试试吧！没测试！ 

[Billy]

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)

[中国风]

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=@StartDate

while @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
*/

[AcHerat]

[Quote=引用 5 楼 roy_88 的回复:]
SQL code
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……
[/Quote]

学习。。。

[Roni]

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

[missukiss02]

先谢了，我去试试，等下回来

[zhongyh1024]

[liang145]

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) end

if @startMonth<>'' and  @endMonth<>'' and @startMonth>@endMonth 
begin
select '开始时间不能大于结束时间'
return
end

set @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'

[liang145]

奥。。不好意思把上边的

alter PROCEDURE [dbo].[TestA]

改成

create PROCEDURE [dbo].[TestA]

[rookie91]

学习一下，谢谢