--仓库网点报表
ALTER proc sp_ReportClientStock @DeptID varchar(5),@StartTime varchar(20),@ShortName varchar(50)
as
begin
---数量/车型/销售商
select count(*) as AccountStock,a.SortTypeName,b.ClientName into #tempClientStock from TrueStockDayhist a,Client b
where datediff(day,a.noteDate,@StartTime)=0
and b.ClientID=a.ExistCustID and ExistCustID<>'' and a.ClientName=@ShortName
group by a.SortTypeName,b.ClientName
select id=identity(int,0,1),gid=0
,值=',sum(case ClientName when '''
+ClientName+''' then 1 else 0 end) as [' +ClientName+']'
into # from(select distinct ClientName from Client) a
--判断需要多少个变量来处理
select @i=max(len(值)) from #
print @i
set @i=7800/@i
--分组临时表
update # set gid=id/@i
select @i=max(gid) from #
--生成数据处理语句
select @sqlhead='''select SortTypeName'''
,@sqlend=''' from #tempClientStock group by SortTypeName'''
,@sql1='',@sql2='select ',@sql3='',@sql4=''
while @i>=0
select @ic=cast(@i as varchar),@i=@i-1
,@sql1='@'+@ic+' varchar(8000),'+@sql1
,@sql2=@sql2+'@'+@ic+'='''','
,@sql3='select @'+@ic+'=@'+@ic+'+a from # where gid='+@ic
+char(13)+@sql3
,@sql4=@sql4+',@'+@ic