--查询处理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
')
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
')
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
--*/
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
')
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
--*/