sql 按月 交叉 动态行列

 

USE [SPP_CentreYou_Test]
GO
/****** Object:  StoredProcedure [dbo].[WX_YM_REPORT]    Script Date: 09/25/2010 15:12:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[WX_YM_REPORT]
(
 @m int,
 @site nvarchar(4000),
    @brand nvarchar(4000),
   @service nvarchar(4000),
    @flag nvarchar(50)
)
as
begin

declare @bc varchar(50)

if @flag='C'
begin
set @bc='CCSSITENAME'
 exec('select b.CCSSITENAME,a.Brand,COUNT(*) as fcount,
 CONVERT(varchar(7),a.ReturnDate,120) as yymm into ##temp
from SPP_P_SGL_HIS as a ,GSR_C_CTS as b where b.CCSSITECODE=a.SITECODE and datediff(m,a.ReturnDate,getdate()) <'+@m+' and a.ReturnDate<GETDATE()
and b.CCSSITECODE in('+@site+') and a.brand in('+@brand+') and a.ServiceSubType in('+@service+')
 group by  b.CCSSITENAME,a.Brand, CONVERT(varchar(7),a.ReturnDate,120)') 
 end
 else if @flag='B'
 begin
 set @bc='CCSBRANCH'
  exec('select b.CCSBRANCH,a.Brand,COUNT(*) as fcount,
 CONVERT(varchar(7),a.ReturnDate,120) as yymm into ##temp
from SPP_P_SGL_HIS as a ,GSR_C_CTS as b where b.CCSSITECODE=a.SITECODE and datediff(m,a.ReturnDate,getdate()) <'+@m+' and a.ReturnDate<GETDATE()
and b.CCSBRANCH in('+@site+') and a.brand in('+@brand+') and a.ServiceSubType in('+@service+')
 group by  b.CCSBRANCH,a.Brand, CONVERT(varchar(7),a.ReturnDate,120)') 
 end
 
 declare @sql varchar(8000)
set @sql = 'select 
  case when grouping('+@bc+')=1 and grouping(brand)=1 then ''合计'' else '+@bc+' end ''维修点名称'',
   case when grouping('+@bc+')=0 and grouping(brand)=1 then ''小计'' else brand end ''品牌'','

select @sql = @sql +  'sum(case yymm when '''+yymm+''' 
                          then fcount else 0 end) as '''+yymm+''','
 from (select distinct top (@m) yymm from ##temp order by yymm ) as a
select @sql = left(@sql,len(@sql)-1) + ',sum(fcount) as ''小计'' from ##temp  group by rollup('+@bc+',brand) order by '+@bc+' desc
'
exec(@sql)
drop table ##temp
end