高手才能解决的问题呀！！！那位高手帮帮忙

[javaTNT]

表：
A B C
1 a 20
1 b 30
1 c 40
2 d 50
2 a 60
转换为如下表结构：
A＃ B1 C1 B2 C2 B3 C3 B4 C4
1 a 20 b 30 c 40 null null
2 a 60 null null null null d 50
请问如何能用sql语句实现？

[zjcxc]

--查询处理

declare @s varchar(8000),@i int
select @s='',@i=max(i)
from(select i=count(*) from 表 group by a)a
while @i>0
select @s=',B'+cast(@i as varchar)
+'=max(case i when '+cast(@i as varchar)
+' then B end),C'+cast(@i as varchar)
+'=max(case i when '+cast(@i as varchar)
+' then C end)'+@s
,@i=@i-1
exec('select A'+@s+'
from(
select a,b,c
,i=(select sum(1) from 表 where a=a.a and b<=a.b and c<=a.c)
from 表 a
)a group by A
')

[zjcxc]

--测试

--测试数据
create table 表(A int,B char(1),C int)
insert 表 select 1,'a',20
union all select 1,'b',30
union all select 1,'c',40
union all select 2,'d',50
union all select 2,'a',60
go

--处理
declare @s varchar(8000),@i int
select @s='',@i=max(i)
from(select i=count(*) from 表 group by a)a
while @i>0
select @s=',B'+cast(@i as varchar)
+'=max(case i when '+cast(@i as varchar)
+' then B end),C'+cast(@i as varchar)
+'=max(case i when '+cast(@i as varchar)
+' then C end)'+@s
,@i=@i-1
exec('select A'+@s+'
from(
select a,b,c
,i=(select sum(1) from 表 where a=a.a and b<=a.b and c<=a.c)
from 表 a
)a group by A
')
go

--删除测试
drop table 表

/*--测试结果
A B1 C1 B2 C2 B3 C3
----------- ---- ----------- ---- ----------- ---- -----------
1 a 20 b 30 c 40
2 d 60 NULL NULL NULL NULL
--*/

[lovvver]

建议搜索，参考行列转换的帖子。
这里多的是。

[zjcxc]

--上面的处理方法写错了,改一下:

--处理
declare @s varchar(8000),@i int
select @s='',@i=count(distinct b)
from 表
while @i>0
select @s=',B'+cast(@i as varchar)
+'=max(case i when '+cast(@i as varchar)
+' then B end),C'+cast(@i as varchar)
+'=max(case i when '+cast(@i as varchar)
+' then C end)'+@s
,@i=@i-1
exec('select A'+@s+'
from(
select a,b,c
,i=(select count(distinct b) from 表 where b<=a.b)
from 表 a
)a group by A
')

[zjcxc]

--测试

--测试数据
create table 表(A int,B char(1),C int)
insert 表 select 1,'a',20
union all select 1,'b',30
union all select 1,'c',40
union all select 2,'d',50
union all select 2,'a',60
go

--处理
declare @s varchar(8000),@i int
select @s='',@i=count(distinct b)
from 表
while @i>0
select @s=',B'+cast(@i as varchar)
+'=max(case i when '+cast(@i as varchar)
+' then B end),C'+cast(@i as varchar)
+'=max(case i when '+cast(@i as varchar)
+' then C end)'+@s
,@i=@i-1
exec('select A'+@s+'
from(
select a,b,c
,i=(select count(distinct b) from 表 where b<=a.b)
from 表 a
)a group by A
')
go

--删除测试
drop table 表

/*--测试结果
A B1 C1 B2 C2 B3 C3 B4 C4
----------- ---- ----------- ---- ----------- ---- ----------- ---- -----------
1 a 20 b 30 c 40 NULL NULL
2 a 60 NULL NULL NULL NULL d 50
--*/

[capcom]

学习~

[PPLUNCLE]

收藏.....