1 --monList monList monList 2 --根据起止时间生成月份列表 3 DECLARE 4 @BeginMonth varchar(6), 5 @EndMonth varchar(6) 6 SELECT 7 @BeginMonth='201409', 8 @EndMonth='201506'SELECT 9 CONVERT(VARCHAR(6),DATEADD(month,number,@BeginMonth+'01'),112) as monList 10 FROM 11 master..spt_values 12 WHERE 13 type='P' 14 and 15 DATEADD(month,number,@BeginMonth+'01')<=@EndMonth+'01' 16 17 --dayList dayList dayList 18 --根据起止时间生成天列表 19 DECLARE @BeginDay DATE,@EndDay DATE 20 SELECT @BeginDay='2016-12-31',@EndDay='2017-10-31'--GetDate() 21 SELECT DateAdd(day,number,@BeginDay) as dayList 22 FROM master..spt_values 23 WHERE type = 'p' 24 AND number <= DateDiff(day,@BeginDay,@EndDay) 25 26 27 28 declare @sql varchar(8000) 29 set @sql = 'select Date' 30 select @sql = @sql + ',isnull (sum(case item when '''+item+''' then saleqty end),0) as ['+item+']' from (select distinct item from test where item is not null) as a 31 select @sql = @sql+' from test group by date' 32 exec(@sql)
