declare @prdtid char(7)
declare @date_str char(4)
declare @date_int int
declare @sql nvarchar(3000)
declare @bigc int
declare @cop char(2)

set @bigc=1
set @cop='02'

if object_id('tempdb.dbo.#class') is null
begin
?create table #class
?(
??ClassID nvarchar(15),
??bigc int
?)
end
truncate table #class
insert into #class(ClassID,bigc)
SELECT ClassID,bigc FROM FS_MS_ProductsClass WHERE bigc=@bigc
--select ClassID,bigc from #class

if object_id('tempdb.dbo.#prdt') is null
begin
?create table #prdt
?(
??prdt char(7),
??ClassID nvarchar(15),
??bigc int
?)
end
truncate table #prdt
insert into #prdt(prdt,ClassID,bigc)
SELECT prdtid,ClassID,@bigc FROM FS_MS_Products WHERE ClassID in (select ClassID from #class)
--select prdt,ClassID,bigc from #prdt

set @date_int=1101
while @date_int<=1112
begin
?set @date_str=LTRIM(RTRIM(CAST(@date_int as NCHAR)))
?if @date_int=1101
?begin
??set @sql=N'select '+RIGHT(@date_str,2)+' as ''月份'', SUM(num*price) as ''金額'' FROM [szjdw].[dbo].[mf_prdtorder_dede] where prdtid in (select prdt from #prdt) and SUBSTRING(order_id,2,2)='''+@cop+''' and SUBSTRING(order_id,4,4)='''+@date_str+''''
?end
?else
?begin
??set @sql=@sql+' union '+'select '+RIGHT(@date_str,2)+' as ''月份'', SUM(num*price) as ''金額'' FROM [szjdw].[dbo].[mf_prdtorder_dede] where prdtid in (select prdt from #prdt) and SUBSTRING(order_id,2,2)='''+@cop+''' and SUBSTRING(order_id,4,4)='''+@date_str+''''
?end
?set @date_int=@date_int+1
end
--select @sql
exec(@sql)
drop table #prdt,#class

可惜的是紅色部分不能寫到外面來，郁悶！

按照月份來統計編號是1的大類的銷售金額