--如果对于每一组x,y列的值不重复,可以用:declare @s varchar(8000),@i int
select @s='',@i=max(ii)
from(select ii=count(*) from 表 group by x)a
while @i>0
select @s=',P'+cast(@i as varchar)+'=max(case id when '
+cast(@i as varchar)+' then y end)'+@s
,@i=@i-1
exec('select p=x'+@s+' from 表 group by x')
select @s='',@i=max(ii)
from(select ii=count(*) from 表 group by x)a
while @i>0
select @s=',P'+cast(@i as varchar)+'=max(case id when '
+cast(@i as varchar)+' then y end)'+@s
,@i=@i-1
exec('select p=x'+@s+' from 表 group by x')
declare @s varchar(8000),@i int
select @s='',@i=max(ii)
from(select ii=count(*) from 表 group by x)a
while @i>0
select @s=',P'+cast(@i as varchar)+'=max(case id when '
+cast(@i as varchar)+' then y end)'+@s
,@i=@i-1
exec('select p=x'+@s+'
from(
select *,id=(select count(*) from 表 where x=a.x and y<=a.y)
from 表 a
)a group by x')
create table 表(x int,y varchar(10))
insert 表 select 1,'a'
union all select 1,'b'
union all select 1,'c'
union all select 1,'d'
union all select 1,'e'
union all select 1,'f'
union all select 1,'g'
union all select 2,'c'
union all select 2,'d'
union all select 2,'g'
go--查询
declare @s varchar(8000),@i int
select @s='',@i=max(ii)
from(select ii=count(*) from 表 group by x)a
while @i>0
select @s=',P'+cast(@i as varchar)+'=max(case id when '
+cast(@i as varchar)+' then y end)'+@s
,@i=@i-1
exec('select p=x'+@s+'
from(
select *,id=(select count(*) from 表 where x=a.x and y<=a.y)
from 表 a
)a group by x')
go--删除测试
drop table 表/*--测试结果p P1 P2 P3 P4 P5 P6 P7
----- ---- ---- ---- ---- ---- ---- -----
1 a b c d e f g
2 c d g NULL NULL NULL NULL--*/
declare @s varchar(8000),@i int
select @s='',@i=max(ii)
from(select ii=count(*) from 表 group by x)a
while @i>0
select @s=',P'+cast(@i as varchar)+'=max(case id when '
+cast(@i as varchar)+' then y end)'+@s
,@i=@i-1
exec('
select id=identity(int,1,1),* into #t from 表
select p=x'+@s+'
from(
select x,y,id=(select count(*) from #t where x=a.x and id<=a.id)
from #t a
)a group by x')