该死的交叉表

[coffeewar]

表内容:
x y

1 a
1 b
1 c
1 d
1 e
1 f
1 g
2 c
2 d
2 g
如何转换成:
p P1 P2 P3 P4 P5

1 a b c d e
2 c d g null null

谢谢!

[zjcxc]

--如果对于每一组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')

[zjcxc]

--少写了一部分东西
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')

[zjcxc]

--测试

--测试数据
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

--*/

[zjcxc]

--如果不满足每组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 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')

[fengyaner]

就是厉害

[coffeewar]

每次都能及时得到你的帮助，非常感谢！