insert into @tab1 select count(*)from checkview group by checktypename ,checktype,checkdate,checkid having checktype='值次' and @checktime=cast(substring(cast(checkdate as nvarchar(20)),1,10) as datetime) and checkid=@checkid
set @i=(select count(*) from @tab1)
end
else if(@checktype=1)
begin
insert into @tab1 select count(*)from checkview group by checktypename ,checktype,checkdate,checkid having checktype='值次' and substring(cast(@checktime as nvarchar(20)),1,7)=substring(cast(checkdate as nvarchar(20)),1,7) and checkid=@checkid
set @i=(select count(*) from @tab1)
endcreate table #tab (id int identity(1,1))
declare @count int
declare @sql nvarchar(1000)
declare @name varchar(20)
set @count=0
set @i=@i*2+3-2
while(@count<@i)
begin
set @name='Para'+convert(varchar(20),@count)
set @sql='
alter table #tab add '+@name+' varchar(20)'
exec sp_executesql @sql
set @count=@count+1
end
转换格式后如下:
while @@fetch_status=0
begin
if(@checkteamid<>@para0)
begin
set @sqlstr='insert into #tab values('+@value+')'--向临时表插入新数据
exec sp_executesql @sqlstr
set @checkteamid=@para0
set @value=@aa+@para0+@aa+','+@aa+@para1+@aa+','+@aa+@para2+@aa+','+@aa+@para3+@aa+','+@aa+@para4+@aa
set @flag=1
set @paraname=@para1
fetch next from curtemp into @para0,@para1,@para2,@para3,@para4,@para5 end
else if(@paraname<>@para1)
begin
set @sqlstr='insert into #tab values('+@value+')'--向临时表插入新数据
exec sp_executesql @sqlstr
set @checkteamid=@para0
set @value=@aa+@para0+@aa+','+@aa+@para1+@aa+','+@aa+@para2+@aa+','+@aa+@para3+@aa+','+@aa+@para4+@aa
set @flag=1
set @paraname=@para1
fetch next from curtemp into @para0,@para1,@para2,@para3,@para4 ,@para5
end
else if(@flag=1)
begin
set @value=@value+','+@aa+@para3+@aa+','+@aa+@para4+@aa
fetch next from curtemp into @para0,@para1,@para2,@para3,@para4,@para5
end
end
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货