34,590
社区成员
发帖
与我相关
我的任务
分享
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
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+'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 100
insert into tb select 200
insert into tb select 300
insert into tb select 400
insert into tb select 500
select id=identity(int,1,1),col into # from tb
declare @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,#
insert into 表2(id,1,2,3,4,5,...,原表最大行数) select @youruserid,* from ##2