--试..if exists(select 1 from sysobjects where id=object_id('f02')) drop Table f02gocreate table f02(yDate smalldatetime,yIndex varchar(10),yVal dec(20,10))insert into f02 select '2006-9-8 01:33:34','1','234.52578' union all select'2006-9-8 01:33:34','2','352.86792' union all select'2006-9-8 01:33:34','3','.875628456' union all select'2006-9-8 02:34:35','1','252.67984' union all select'2006-9-8 02:34:35','2','287.86874' union all select'2006-9-9 02:34:35','3','.875623576'go
if exists(select 1 from sysobjects where id=object_id('p_InsertTable')) drop Proc p_InsertTablego
Create Proc p_InsertTable@getdate datetime=nullasdeclare @s varchar(8000),@date varchar(10),@date2 varchar(10)set @getdate =isnull(@getdate,getdate())set @date = convert(char(8),@getdate,112)set @date2 = convert(char(8),@getdate+1,112)set @s='select 时间=datename(hour,yDate)'select @s=@s+',流量'+Rtrim(yIndex)+'=sum(case when yIndex='''+yIndex+''' then yVal else 0 end)' from f02 group by yIndexset @s=@s+' from f02 where yDate >= '''+ @date +''' and yDate < '''+ @date2 +''' group by datename(hour,yDate)'exec(@s)go--exec dbo.p_InsertTable exec dbo.p_InsertTable '20060909'
