declare @s nvarchar(max)

select  @s=isnull(@s+' union all select ',' select ')+quotename(Name)+' from 表名' from syscolumns where ID=object_ID('表名')
exec('insert   NewTable (COl)'+@s)

------解决方案--------------------

举个10列数据抽取5列做行列转换的例子,不知是不是楼主所要的,
SQL code

create table t5(id int,a int,b int,c int,d int,e int,f int,g int,h int,i int,j int)

insert into t5 
select 101,1,2,3,4,5,6,7,8,9,10 union all
select 102,11,22,33,44,55,66,77,88,99,1010

select id,b,d,f,h,j from t5
id          b           d           f           h           j
----------- ----------- ----------- ----------- ----------- -----------
101         2           4           6           8           10
102         22          44          66          88          1010


select id,c
from (select id,b,d,f,h,j from t5) t
unpivot (c for cc in (b,d,f,h,j)) ut

id          c
----------- -----------
101         2
101         4
101         6
101         8
101         10
102         22
102         44
102         66
102         88
102         1010