表1数据select * from 表1
结果:字段1
------------
100
200
300
...能不能通过一条语句写入一个临时表2表2结构
u_1 int,u_2 int,u_3 int.....(按表1的行数动态增加 :u_? int)
结果:字段1
------------
100
200
300
...能不能通过一条语句写入一个临时表2表2结构
u_1 int,u_2 int,u_3 int.....(按表1的行数动态增加 :u_? int)
insert into tb select 2
insert into tb select 2
insert into tb select 4
insert into tb select 7
insert into tb select 34
insert into tb select 23
insert into tb select 44
insert into tb select 12
insert into tb select 3
insert into tb select 6
insert into tb select 8
insert into tb select 19
go
select id=identity(int,1,1),col into ##1 from tb
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+'['+ ltrim(id) +']' from(
select distinct [id] from ##1
)t
exec('select '+@s+'from ##1 pivot (max([col]) for id in('+@s+'))b')/*
1 2 3 4 5 6 7 8 9 10 11 12
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2 2 4 7 34 23 44 12 3 6 8 19(1 行受影响)
*/
go
drop table tb,##1
修正:
create table tb(col int)
insert into tb select 2
insert into tb select 2
insert into tb select 4
insert into tb select 7
insert into tb select 34
insert into tb select 23
insert into tb select 44
insert into tb select 12
insert into tb select 3
insert into tb select 6
insert into tb select 8
insert into tb select 19
go
select id=identity(int,1,1),col into ##1 from tb
declare @s nvarchar(4000)
select @s=isnull(@s+',','')+'['+ ltrim(id) +']' from(
select distinct [id] from ##1
)t
exec('select '+@s+' into ##2 from ##1 pivot (max([col]) for id in('+@s+'))b')
select * from ##2
/*
1 2 3 4 5 6 7 8 9 10 11 12
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
2 2 4 7 34 23 44 12 3 6 8 19(1 行受影响)
*/
go
drop table tb,##1,##2
我想qianjin036a的方法一定是可以的,非常感谢,可能是ms2000不支持 pivot的原因我冒昧的问一下如果,我的表2是存在的实表,
结构如下
u_id,u_field1,u_field2....u_field40我想将表1的数据从u_field1开始写入 (可以保证表1的行数不会大于40)
请教一下语句怎么写,不用游标?表1数据select * from 表1
结果:字段1
------------
100
200
300
...
select * from ##2
改成:
insert into 表2(id,1,2,3,4,5,...,原表最大行数) select @youruserid,* from ##2
insert into tb select 100
insert into tb select 200
insert into tb select 300
insert into tb select 400
insert into tb select 500select id=identity(int,1,1),col into # from tbdeclare @sql varchar(8000)
set @sql = ''
select @sql = @sql + ',(select '+ltrim(col)+' [u_'+ltrim(id)+']) a'+ltrim(id)
from #
set @sql =' select * from '+stuff(@sql,1,1,'')
print (@sql)
exec(@sql)
--select * from (select 100 [u_1]) a1,(select 200 [u_2]) a2,(select 300 [u_3]) a3,(select 400 [u_4]) a4,(select 500 [u_5]) a5 drop table tb,#