表1:
ID stockID tradedate money
1 A 2011-3-31 50
2 B 2011-3-31 -45
3 A 2011-4-1 25
4 B 2011-4-2 23求一存储过程,输入开始和结束日期,比如开始日期2011-3-29,结束日期2011-4-2得到以下结果:stockID 2011-03-29 2011-03-30 2011-03-31 2011-04-1 2011-04-02
A 0 0 50 25 0
B 0 0 -45 0 23就是说,如果表1在开始结束日期中money没有数据,就设为0
非常感谢!
ID stockID tradedate money
1 A 2011-3-31 50
2 B 2011-3-31 -45
3 A 2011-4-1 25
4 B 2011-4-2 23求一存储过程,输入开始和结束日期,比如开始日期2011-3-29,结束日期2011-4-2得到以下结果:stockID 2011-03-29 2011-03-30 2011-03-31 2011-04-1 2011-04-02
A 0 0 50 25 0
B 0 0 -45 0 23就是说,如果表1在开始结束日期中money没有数据,就设为0
非常感谢!
create table tb(id int,stockid varchar(10),tradedate datetime,[money] int)
insert into tb
select 1 ,'A', '2011-3-31', 50 union all
select 2 ,'B', '2011-3-31', -45 union all
select 3 ,'A', '2011-4-1', 25 union all
select 4 ,'B', '2011-4-2', 23
gocreate proc get_all
(
@start datetime,
@end datetime
)
as
begin
declare @sql varchar(8000)
select dateadd(dd,number,@start) as date into #t
from master..spt_values
where [type] = 'p' and number between 0 and datediff(dd,@start,@end)set @sql = 'select stockid'
select @sql = @sql + ',sum(case convert(varchar(10),tradedate,120) when ''' + date + ''' then [money] else 0 end)[' + date + ']'
from (select distinct convert(varchar(10),date,120) date from #t)t
select @sql = @sql + ' from tb group by stockid'
exec(@sql)
end
goexec get_all '2011-3-29','2011-4-2'drop proc get_all
drop table tb/*stockid 2011-03-29 2011-03-30 2011-03-31 2011-04-01 2011-04-02
---------- ----------- ----------- ----------- ----------- -----------
A 0 0 50 25 0
B 0 0 -45 0 23(2 行受影响)