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

转载于:https://www.cnblogs.com/dthom/articles/1826869.html