我建的表中的字段都是varchar(10)，运行通过，不知合不合用
if exists(select * from sysobjects where type='P' and name='RowToCol')
drop procedure RowToCol
go
create procedure RowToCol as
begin
declare @create varchar(4000)
declare @ins varchar(1000)
select @create='create table #temp (columnXX varchar(10)',@ins='insert into #temp values(''XX'''
declare @XX varchar(10)
declare xx_cursor cursor for select distinct XX from TableA order by XX
open xx_cursor
fetch next from xx_cursor into @XX
while @@fetch_status = 0
begin
select @create=@create+',[column'+@XX+'] varchar(10) default(''--'')',@ins=@ins+','+@XX
fetch next from xx_cursor into @XX
end
close xx_cursor
deallocate xx_cursor
select @create=@create+')',@ins=@ins+')'
declare @YY varchar(10),@DATA varchar(10)
declare @OldYY varchar(10),@values varchar(1000)
declare yy_cursor cursor for select YY,XX,mData from TableA order by YY
open yy_cursor
fetch next from yy_cursor into @YY,@XX,@DATA
while @@fetch_status = 0
begin
if @oldYY=@YY
select @ins=@ins+',[column'+@XX+']', @values=@values+','+@DATA
else
begin
if @oldYY is not null
select @ins=@ins+')'+@values+')'+char(13)+'insert into #temp(columnXX,[column'+@XX+']',@values=' values(''YY'+@YY+''','+@DATA
else
select @ins=@ins+char(13)+'insert into #temp(columnXX,[column'+@XX+']',@values=' values(''YY'+@YY+''','+@DATA
end
select @oldYY=@YY
fetch next from yy_cursor into @YY,@XX,@DATA
end
close yy_cursor
deallocate yy_cursor
select @ins=@ins+')'+@values+')'
print @create
print @ins
exec(@create+@ins+'select * from #temp')
end
go
RowToCol
