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)
