我以前试过的是将一个长字符串转为一个表的,所以一看到,就想起了这个方法。
下面是代码:
create table #AccountReport(col1 char(8),col2 char(8),col3 char(8),col4 char(8))
DECLARE AccountReport_cursor CURSOR FOR select * from #mms_account
OPEN AccountReport_cursor
FETCH NEXT FROM AccountReport_cursor INTO @serialno
WHILE @@FETCH_STATUS = 0
BEGIN
if @intcount=0
begin
set @col1=@serialno
set @intcount=1
end
else if @intcount=1
begin
set @col2=@serialno
set @intcount=2
end
else if @intcount=2
begin
set @col3=@serialno
set @intcount=3
end
else if @intcount=3
begin
set @col4=@serialno
set @intcount=4
set @intcount=0
insert into #AccountReport(col1,col2,col3,col4)values(@col1,@col2,@col3,@col4)
set @col1=''
set @col2=''
set @col3=''
set @col4=''
end
FETCH NEXT FROM AccountReport_cursor INTO @serialno
END
if @col1<>'' or @col2<>'' or @col3<>'' or @col4<>''
insert into #AccountReport(col1,col2,col3,col4)values(@col1,@col2,@col3,@col4)
CLOSE AccountReport_cursor
DEALLOCATE AccountReport_cursor
select col1,col2,col3,col4 from #AccountReport
drop table #mms_account
drop table #AccountReport