declare @sql varchar(8000)

set @sql = 'select CONVERT(varchar(10), OrderDate, 120) as 日期'

select @sql = @sql + ' , sum(case PayWay when ''' + cast(Pwy as varchar)+ ''' then OrderAmount else 0 end)[' + cast(Pwy2 as varchar) + ']

,sum(case PayWay when ''' + cast(Pwy as varchar)+ ''' then 1 else 0 end)[' + cast(Pwy2 as varchar) + '销量]'

from (select distinct id as Pwy,PayWay as Pwy2 from dbo.A_OrderPayWay) as a

set @sql = @sql + ',sum( case OrderAmount when NULL then 0 else OrderAmount end ) as 合计,count(*) as 总订单   from A_OrderInfo group by


CONVERT(varchar(10), OrderDate, 120) Order by CONVERT(varchar(10), OrderDate, 120) desc'


exec(@sql)

备注

[' + cast(Pwy2 as varchar) + '销量] //列名转换

CONVERT(varchar(10), OrderDate, 120)    //获取订单日期的 年月日部分

sum(case PayWay when ''' + cast(Pwy as varchar)+ ''' then OrderAmount else 0 end)  //统计每种支付方式的当天金额

sum(case PayWay when ''' + cast(Pwy as varchar)+ ''' then 1 else 0 end)  //统计每种支付方式的当天销售数量

select distinct id as Pwy from dbo.A_OrderPayWay  //获取所以支付类型