select id,class,a1 from tb union all select id,class,a2 from tb ...
declare @table table (ID int,Class int,a1 int,a2 int,a3 int,a4 int,a5 int,a6 int) insert into @table select 1,2,0,3,0,9,8,7--手动连接 select ID,Class,a1 from @table UNION ALL select ID,Class,a2 from @table UNION ALL select ID,Class,a3 from @table UNION ALL select ID,Class,a4 from @table UNION ALL select ID,Class,a5 from @table UNION ALL select ID,Class,a6 from @table /* ID Class a1 ----------- ----------- ----------- 1 2 0 1 2 3 1 2 0 1 2 9 1 2 8 1 2 7 */--动态拼接 DECLARE @i INT SET @i=1 DECLARE @sql VARCHAR(300) SET @sql='' WHILE @i<7 BEGIN SET @sql=@sql+'select ID,Class,a'+CAST(@i AS varchar(5))+' from 表名 UNION ALL ' SET @i=@i+1 END SELECT substring(@sql,0,LEN(@sql)-9) EXEC(substring(@sql,0,LEN(@sql)-9)
create table test0127 (ID int,Class int,a1 int,a2 int,a3 int,a4 int,a5 int,a6 int) insert into test0127 select 1,2,0,3,0,9,8,7 --动态拼接 DECLARE @i INT SET @i=1 DECLARE @sql VARCHAR(300) SET @sql='' WHILE @i<7 BEGIN SET @sql=@sql+'select ID,Class,a'+CAST(@i AS varchar(5))+' from test0127 UNION ALL ' SET @i=@i+1 END set @sql=substring(@sql,0,LEN(@sql)-9) EXEC(@sql) /* ID Class a1 ----------- ----------- ----------- 1 2 0 1 2 3 1 2 0 1 2 9 1 2 8 1 2 7 */刚才的有点问题,更正一下。
union all
select id,class,a2 from tb
...
declare @table table (ID int,Class int,a1 int,a2 int,a3 int,a4 int,a5 int,a6 int)
insert into @table
select 1,2,0,3,0,9,8,7--手动连接
select ID,Class,a1 from @table UNION ALL
select ID,Class,a2 from @table UNION ALL
select ID,Class,a3 from @table UNION ALL
select ID,Class,a4 from @table UNION ALL
select ID,Class,a5 from @table UNION ALL
select ID,Class,a6 from @table
/*
ID Class a1
----------- ----------- -----------
1 2 0
1 2 3
1 2 0
1 2 9
1 2 8
1 2 7
*/--动态拼接
DECLARE @i INT
SET @i=1
DECLARE @sql VARCHAR(300)
SET @sql=''
WHILE @i<7
BEGIN
SET @sql=@sql+'select ID,Class,a'+CAST(@i AS varchar(5))+' from 表名 UNION ALL '
SET @i=@i+1
END
SELECT substring(@sql,0,LEN(@sql)-9)
EXEC(substring(@sql,0,LEN(@sql)-9)
create table test0127
(ID int,Class int,a1 int,a2 int,a3 int,a4 int,a5 int,a6 int)
insert into test0127
select 1,2,0,3,0,9,8,7
--动态拼接
DECLARE @i INT
SET @i=1
DECLARE @sql VARCHAR(300)
SET @sql=''
WHILE @i<7
BEGIN
SET @sql=@sql+'select ID,Class,a'+CAST(@i AS varchar(5))+' from test0127 UNION ALL '
SET @i=@i+1
END
set @sql=substring(@sql,0,LEN(@sql)-9)
EXEC(@sql)
/*
ID Class a1
----------- ----------- -----------
1 2 0
1 2 3
1 2 0
1 2 9
1 2 8
1 2 7
*/刚才的有点问题,更正一下。