sql 我的交叉表实现
create proc WX_ZQ
(
@brand nvarchar(500),
@beginDate datetime,
@endDate datetime
)
as
begin
select a.Brand,a.ServiceSubType,sum(datediff(D,a.ApplyDate,a.ReturnDate))/COUNT(*) as AVGDAY into #temp from SPP_P_SGL_HIS as a,ServiceTypeForm as b
where a.Status='shipout' group by a.Brand,a.ServiceSubType
declare @sql varchar(8000)
set @sql = 'select brand as ''品牌'', '
select @sql = @sql + 'sum(case ServiceSubType when '''+ServiceSubType+'''
then avgday else 0 end) as '''+ServiceSubType+''','
from (select distinct ServiceSubType from SPP_P_SGL_HIS) as a
select @sql = left(@sql,len(@sql)-1) + ',sum(avgday) as ''小计'' from #temp group by brand'
exec(@sql)
drop table #temp
end