drop table tb
go
create table tb(a varchar(20),b varchar(20),c varchar(20),d varchar(20))
insert into tb select '1','2','3','4'
insert into tb select '5','6','7','8'
insert into tb select '9','10','11','12'
insert into tb select '13','14','15','16'declare @i int,@sqlresult varchar(8000)
set @sqlresult=''
set @i=1
while @i<=(select count(1) from syscolumns where id=object_id('tb'))
begin
declare @sql varchar(8000),@colname varchar(50)
select @colname=name from syscolumns where id=object_id('tb') and colid=@i
select @sql=isnull(@sql+',','')+' max(case when id='''+ltrim(id)+''' then '+@colname+' end) as [第'+ltrim(id)+'列]'
from(select row_number() over(order by a) as id,* from (select * from tb union all select 'a','b','c','d')t)t
set @sql='select '+@sql+' from (select row_number() over(order by a) as id,* from (select * from tb union all select ''a'',''b'',''c'',''d'')t)t union all '
set @sqlresult=@sqlresult+@sql
set @sql=null
set @i=@i+1
end
set @sqlresult = left (@sqlresult,len(@sqlresult)-10)
exec(@sqlresult)第1列 第2列 第3列 第4列 第5列
1 13 5 9 a
2 14 6 10 b
3 15 7 11 c
4 16 8 12 d
go
create table tb(a varchar(20),b varchar(20),c varchar(20),d varchar(20))
insert into tb select '1','2','3','4'
insert into tb select '5','6','7','8'
insert into tb select '9','10','11','12'
insert into tb select '13','14','15','16'declare @i int,@sqlresult varchar(8000)
set @sqlresult=''
set @i=1
while @i<=(select count(1) from syscolumns where id=object_id('tb'))
begin
declare @sql varchar(8000),@colname varchar(50)
select @colname=name from syscolumns where id=object_id('tb') and colid=@i
select @sql=isnull(@sql+',','')+' max(case when id='''+ltrim(id)+''' then '+@colname+' end) as [第'+ltrim(id)+'列]'
from(select row_number() over(order by a) as id,* from (select * from tb union all select 'a','b','c','d')t)t
set @sql='select '+@sql+' from (select row_number() over(order by a) as id,* from (select * from tb union all select ''a'',''b'',''c'',''d'')t)t union all '
set @sqlresult=@sqlresult+@sql
set @sql=null
set @i=@i+1
end
set @sqlresult = left (@sqlresult,len(@sqlresult)-10)
exec(@sqlresult)第1列 第2列 第3列 第4列 第5列
1 13 5 9 a
2 14 6 10 b
3 15 7 11 c
4 16 8 12 d
create table tb(A int,B int,C int,D int)
insert tb select 1,2 ,3 ,4
union all select 5,6 ,7 ,8
union all select 9,10,11,12
go--查询处理
declare @s1 varchar(8000),@s2 varchar(8000)
,@s3 varchar(8000),@s4 varchar(8000),@s5 varchar(8000)
,@i varchar(10)
select @s1='',@s2='',@s3='',@s4='',@s5='',@i='0'
select @s1=@s1+',@'+@i+' varchar(8000)'
,@s2=@s2+',@'+@i+'=''[字段1]='''''+name+''''''''
,@s3=@s3+'
,@'+@i+'=@'+@i+'+'',[字段''+cast(@i as varchar)+'']=''+cast(['+name+'] as varchar)'
,@s4=@s4+',@'+@i+'=''select ''+@'+@i
,@s5=@s5+'+'' union all ''+@'+@i
,@i=cast(@i as int)+1
from syscolumns
where object_id('tb')=idselect @s1=stuff(@s1,1,1,'')
,@s2=stuff(@s2,1,1,'')
,@s4=stuff(@s4,1,1,'')
,@s5=stuff(@s5,1,15,'')exec('declare '+@s1+',@i int
select '+@s2+'
set @i=1
select @i=@i+1'+@s3+'
from tb
select '+@s4+'
exec('+@s5+')')
go--删除测试表
drop table tb/*--测试结果字段1 字段2 字段3 字段4
---- ----------- ----------- -----------
A 1 5 9
B 2 6 10
C 3 7 11
D 4 8 12（所影响的行数为 4 行）
--*/