原数据:
日期 品牌 规格 产品条码
2006-1-1 A Z 000001
2006-1-1 A Z 000002
2006-1-1 B Z 000003
... ... ... ...
要求统计格式为:根据选择的日期段(比如2006-1-1至2006-1-31)统计产品的数量:
品牌 规格 2006-1-1 2006-1-2 2006-1-3 ... 2006-1-31
日期 品牌 规格 产品条码
2006-1-1 A Z 000001
2006-1-1 A Z 000002
2006-1-1 B Z 000003
... ... ... ...
要求统计格式为:根据选择的日期段(比如2006-1-1至2006-1-31)统计产品的数量:
品牌 规格 2006-1-1 2006-1-2 2006-1-3 ... 2006-1-31
set @sql=''
select @sql=@sql+',['+convert(varchar(10),日期,120)+']=sum(case when convert(varchar(10),日期,120)='''+convert(varchar(10),日期,120)+''' then 1 else 0 end)'
from tablename
group by convert(varchar(10),日期,120)exec('select 品牌,规格'+@sql+' from tablename group by 品牌,规格')
set @sql=''
select @sql=@sql+',['+convert(varchar(10),日期,120)+']=sum(case when convert(varchar(10),日期,120)='''+convert(varchar(10),日期,120)+''' then 1 else 0 end)'
from tablename
where 日期>='2006-1-1' and 日期<'2006-2-1'
group by convert(varchar(10),日期,120)exec('select 品牌,规格'+@sql+' from tablename where 日期>=''2006-1-1'' and 日期<''2006-2-1'' group by 品牌,规格')
Select @StartDate = '2006-1-1', @EndDate = '2006-1-31'
Declare @S Nvarchar(4000)
Select @S = N'Select 品牌, 规格'
Select @S = @S + ' , SUM(Case 日期 When ''' + 日期 + ''' Then 1 Else 0 End) As [' + 日期 + ']'
From TableName Where 日期 Between @StartDate And @EndDate Group By 日期
Select @S = @S + ' From TableName 日期 Between ''' + @StartDate + ''' And ''' + @EndDate + ''' Group By 品牌, 规格'
EXEC(@S)
继续讨论:
如果选择的日期段其中的有的日期没有产量,那么要产量默认为零,怎么处理哪?
-------------------
意思是不是那天在表中沒有數據?
insert into @t select '2006-1-1','A','Z','000001'
union all select '2006-1-1','A','Z','000002'
union all select '2006-1-1','B','Z','000003'select * from @t
pivot
(count(产品条码)
for 日期 in ([2006-1-1],[2006-1-2],[2006-1-3])
) as pit/*品牌 规格 2006-1-1 2006-1-2 2006-1-3
---------- ---------- ----------- ----------- -----------
A Z 2 0 0
B Z 1 0 0(2 行受影响)
*/
drop table tb
gocreate table tb(日期 datetime ,品牌 char(1),规格 char(1))
goinsert into tb select convert(varchar(10),getdate(),120),'A','Z'
union all
select convert(varchar(10),getdate(),120),'A','Z'
union all
select convert(varchar(10),getdate()+1,120),'B','Z'
union all
select convert(varchar(10),getdate(),120),'B','Z'select * from tb--执行查询
declare @Sql varchar(2000),
@BeginDate datetime,
@EndDate datetime,
@CurrDate datetimeselect @BeginDate = getdate(), @EndDate = getdate()+10, @CurrDate = @BeginDateselect @Sql = ' select 品牌,规格 '
while @CurrDate <= @EndDate
begin
select @Sql = @Sql + ', sum( case when 日期 = '''+convert(varchar(10),@CurrDate,120)+''' then 1 else 0 end ) as ['+convert(varchar(10),@CurrDate,120)+']'
select @CurrDate = @CurrDate +1
endselect @Sql = @Sql + ' from tb where 日期 between '''+convert(varchar(10),@beginDate,120) + ''' and ''' +convert(varchar(10),@EndDate,120)+ ''''+
' group by 品牌,规格'print(@Sql)
exec(@Sql)
如果选择的日期段其中的有的日期没有产量,那么要产量默认为零,怎么处理哪?
-------------------
循环生成@sqldeclare @sql varchar(8000)
set @sql=''
declare @日期 datetime
set @日期='2006-1-1'
while @日期<'2006-2-1'
begin
set @sql=@sql+',['+convert(varchar(10),@日期,120)+']=sum(case when convert(varchar(10),日期,120)='''+convert(varchar(10),@日期,120)+''' then 1 else 0 end)'
set @日期=dateadd(day,1,@日期)
endexec('select 品牌,规格'+@sql+' from tablename where 日期>=''2006-1-1'' and 日期<''2006-2-1'' group by 品牌,规格')
(日期 DateTime,
品牌 Varchar(10),
规格 Varchar(10),
产品条码 Varchar(10))
Insert TEST Select '2006-1-1', 'A', 'Z', '000001'
Union All Select '2006-1-1', 'A', 'Z', '000002'
Union All Select '2006-1-1', 'B', 'Z', '000003'
Union All Select '2006-2-1', 'B', 'Z', '000003'
Union All Select '2006-3-1', 'B', 'Z', '000003'
GO
Create Procedure SP_TEST
(@StartDate DateTime,
@EndDate DateTime)
As
Begin
Select Top 1000 Identity(Int, 0, 1) As ID Into #T from Sysobjects A, Sysobjects B Declare @S Nvarchar(4000)
Select @S = N'Select 品牌, 规格'
Select @S = @S + N' , SUM(Case Convert(Varchar(10), 日期, 120) When ''' + Convert(Varchar(10), 日期, 120) + ''' Then 1 Else 0 End) As [' + Convert(Varchar(10), 日期, 120) + ']'
From (Select DateAdd(dd, ID, @StartDate) As 日期 From #T Where ID <= DateDiff(dd, @StartDate, @EndDate)) A
Select @S = @S + N' From TEST Group By 品牌, 规格'
EXEC(@S) Drop Table #T
End
GO
EXEC SP_TEST @StartDate = '2006-1-1', @EndDate = '2006-1-31'
GO
Drop Table TEST
Drop Procedure SP_TEST
--Result
/*
品牌 规格 2006-01-01 2006-01-02 2006-01-03 ... 2006-01-30 2006-01-31
A Z 2 0 0 0 0
B Z 1 0 0 0 0
*/