流行模式

declare @str varchar(8000)

set @str=''
select @str=@str+',['+省份+']'+
'=sum(case 省份 when ''' + 省份 + ''' then round(体积,2) else 0 end)'
from 销售主表,销售明细 where 销售主表.销售单号=销售明细.销售单号 and 销售日期 between ':开始日期' and ':截止日期'
group by 省份

declare @str1 varchar(8000)
set @str1=''
select @str1=@str1+',['+省份+']'+
'=sum(case 省份 when ''' + 省份 + ''' then 1 else 0 end) '
from 销售主表 where 销售日期 between ':开始日期' and ':截止日期'
group by 省份

declare @str2 varchar(8000)
set @str2=''
select @str2=@str2+',['+省份+']'+
'=sum(case 省份 when ''' + 省份 + ''' then 售价合计 else 0 end) '
from 销售主表,销售明细 where 销售主表.销售单号=销售明细.销售单号 and 销售日期 between ':开始日期' and ':截止日期'
group by 省份



exec('select isnull([产品名称],''数量合计:'') as 省份'+@str
+',sum(round(体积,2)) as [合计] from 销售主表,销售明细 where 销售主表.销售单号=销售明细.销售单号 and 销售日期 between '':开始日期'' and '':截止日期'' group by [产品名称] with rollup '
+' union all '
+'select (''销据合计:'') as 省份'+@str1
+',count(销售单号) as [合计] from 销售主表 where 销售日期 between '':开始日期'' and '':截止日期'' '
+' union all '
+'select (''金额合计:'') as 省份'+@str2
+',sum(售价合计) as [合计] from 销售主表,销售明细 where 销售主表.销售单号=销售明细.销售单号 and 销售日期 between '':开始日期'' and '':截止日期'' '

)