27,579
社区成员
发帖
与我相关
我的任务
分享
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
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10),
col5 varchar(10),
col6 varchar(10),
col7 varchar(10),
col8 varchar(10),
col9 varchar(10),
col10 varchar(10)
)
go
insert into tb select '1','2','3','4','5','6','7','8','9','10'
declare @str varchar(max)
select @str=isnull(@str+' union all '+char(10),'')+'select '+name+' from tb' from sys.columns where object_id=object_id('tb','U')
exec(@str)
/*
col1
----------
1
2
3
4
5
6
7
8
9
10
(10 行受影响)
*/
或
declare @s nvarchar(max)
select @s=isnull(@s+' union all select ',' select ')+quotename(Name)+' from 表名' from syscolumns where ID=object_ID('表名')
insert into NewTable (COl) exec(@s)
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)