--
测试数据
create table tb( O char ( 1 ),X varchar ( 10 ),Y varchar ( 10 ),Z varchar ( 10 ))
insert tb select ' A ' , ' x1 ' , ' y1 ' , ' z1 '
union all select ' B ' , ' x2 ' , ' y2 ' , ' z2 '
union all select ' C ' , ' x3 ' , ' y3 ' , ' z3 '
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 + ' = '' O= ''''' + name + ''''''''
, @s3 = @s3 + ' ,@ ' + @i + ' =@ ' + @i + ' + '' ,[ '' +cast([O] 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 ' ) = id and colid > 1
order by colid
select
@s1 = stuff ( @s1 , 1 , 1 , '' )
, @s2 = stuff ( @s2 , 1 , 1 , '' )
, @s3 = stuff ( @s3 , 1 , 1 , '' )
, @s4 = stuff ( @s4 , 1 , 1 , '' )
, @s5 = stuff ( @s5 , 1 , 15 , '' )
select * from tb
/*
O X Y Z
---------------------------
A x1 y1 z1
B x2 y2 z2
C x3 y3 z3
*/
exec ( ' declare ' + @s1 + '
select ' + @s2 + '
select ' + @s3 + '
from tb
select ' + @s4 + '
exec( ' + @s5 + ' ) ' )
/*
O A B C
-------------------------------
X x1 x2 x3
Y y1 y2 y3
Z z1 z2 z3
*/
drop table tb
create table tb( O char ( 1 ),X varchar ( 10 ),Y varchar ( 10 ),Z varchar ( 10 ))
insert tb select ' A ' , ' x1 ' , ' y1 ' , ' z1 '
union all select ' B ' , ' x2 ' , ' y2 ' , ' z2 '
union all select ' C ' , ' x3 ' , ' y3 ' , ' z3 '
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 + ' = '' O= ''''' + name + ''''''''
, @s3 = @s3 + ' ,@ ' + @i + ' =@ ' + @i + ' + '' ,[ '' +cast([O] 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 ' ) = id and colid > 1
order by colid
select
@s1 = stuff ( @s1 , 1 , 1 , '' )
, @s2 = stuff ( @s2 , 1 , 1 , '' )
, @s3 = stuff ( @s3 , 1 , 1 , '' )
, @s4 = stuff ( @s4 , 1 , 1 , '' )
, @s5 = stuff ( @s5 , 1 , 15 , '' )
select * from tb
/*
O X Y Z
---------------------------
A x1 y1 z1
B x2 y2 z2
C x3 y3 z3
*/
exec ( ' declare ' + @s1 + '
select ' + @s2 + '
select ' + @s3 + '
from tb
select ' + @s4 + '
exec( ' + @s5 + ' ) ' )
/*
O A B C
-------------------------------
X x1 x2 x3
Y y1 y2 y3
Z z1 z2 z3
*/
drop table tb