create table employee(name varchar(6))insert into employee select '张三' union all select '李四' union all select '王五' union all select '赵六' declare @x varchar(500),@y varchar(500),@sql varchar(2000)with t as (select row_number() over(order by getdate()) rn, name from employee) select @x=stuff( cast((select '['+cast(rn as varchar)+'],' from t for xml path('')) as varchar(500)), max(rn)*4,1,''), @y=cast((select '['+cast(rn as varchar)+'] name'+cast(rn as varchar)+',' from t for xml path('')) as varchar(500)) from tselect @sql='select '+left(@y,len(@y)-1)+' from (select row_number() over(order by getdate()) rn, name from employee) t ' + ' pivot(max(name) for rn in ('+@x+')) t2'exec(@sql)name1 name2 name3 name4 ------ ------ ------ ------ 张三 李四 王五 赵六(1 row(s) affected)
name code
张三 44
李四 33
王五 22
赵六 11 code1 code2 code3 code 4
44 33 22 11
如果我还要根据code排序呢
create table employee(name varchar(6))insert into employee
select '张三' union all
select '李四' union all
select '王五' union all
select '赵六'
declare @x varchar(500),@y varchar(500),@sql varchar(2000)with t as
(select row_number() over(order by getdate()) rn,
name from employee)
select
@x=stuff(
cast((select '['+cast(rn as varchar)+'],' from t for xml path('')) as varchar(500)),
max(rn)*4,1,''),
@y=cast((select '['+cast(rn as varchar)+'] name'+cast(rn as varchar)+',' from t for xml path('')) as varchar(500))
from tselect @sql='select '+left(@y,len(@y)-1)+' from (select row_number() over(order by getdate()) rn, name from employee) t '
+ ' pivot(max(name) for rn in ('+@x+')) t2'exec(@sql)name1 name2 name3 name4
------ ------ ------ ------
张三 李四 王五 赵六(1 row(s) affected)