有一个已经存在的原表如下:
Style Country factory buydate qty
AAAA china fengtai 3/1/2012 500
AAAA china fengtai 4/1/2012 300
BBBB US BAOTAI 2/1/2012 400
CCCC china TIANFA 5/1/2012 899
CCCC china TIANFA 4/1/2012 211
想得到如下格式的表(最好是能生成一个临时表#tab1,但是因为列数是动态的不能缺定(因为原表buydate不同日期的个数不同),所以不知如何做):
Style Country factory Feb.12 Mar.12 Apr.12 May.12
AAAA China fengtai 0 500 300 0
BBBB US BAOTAI 400 0 0 0
CCCC China TAIFAFA 0 0 211 899
该如何写存储过程?谢谢
Style Country factory buydate qty
AAAA china fengtai 3/1/2012 500
AAAA china fengtai 4/1/2012 300
BBBB US BAOTAI 2/1/2012 400
CCCC china TIANFA 5/1/2012 899
CCCC china TIANFA 4/1/2012 211
想得到如下格式的表(最好是能生成一个临时表#tab1,但是因为列数是动态的不能缺定(因为原表buydate不同日期的个数不同),所以不知如何做):
Style Country factory Feb.12 Mar.12 Apr.12 May.12
AAAA China fengtai 0 500 300 0
BBBB US BAOTAI 400 0 0 0
CCCC China TAIFAFA 0 0 211 899
该如何写存储过程?谢谢
set @sql = 'select Style ,Country ,factory '
select @sql = @sql + ' , max(case buydate when ''' + buydate + ''' then qty else 0 end) [' + buydate + ']'
from (select distinct convert(varchar(10),buydate,120) buydate from tb) as a
set @sql = @sql + ' from (select Style ,Country ,factory ,convert(varchar(10),buydate,120) buydate , qty from tb) t group by Style ,Country ,factory'
exec(@sql)
declare @sql varchar(8000)
set @sql = 'select Style ,Country ,factory '
select @sql = @sql + ' , sum(case buydate when ''' + buydate + ''' then qty else 0 end) [' + buydate + ']'
from (select distinct convert(varchar(10),buydate,120) buydate from tb) as a
set @sql = @sql + ' from (select Style ,Country ,factory ,convert(varchar(10),buydate,120) buydate , qty from tb) t group by Style ,Country ,factory'
exec(@sql)
也就是说不管是哪个目的地国家,只要style,country,factory相同,就把同一个月所有的qty求和。