--查询处理(因为不知道表中是否有主键,所以用临时表处理)
select id=identity(int,1,1),* into #t from tb
declare @s varchar(8000),@i int
select @s='',@i=max(ii)
from(
select ii=count(*) from tb
group by H1,H2,H3
)a
while @i>1
select @s=',H4'+cast(@i as varchar)+'=max(case sid when '
+cast(@i as varchar)+' then H4 end),H5'+cast(@i as varchar)
+'=max(case sid when '+cast(@i as varchar)+' then H5 end)'+@s
,@i=@i-1
exec('select H1,H2,H3
,H4=max(case sid when 1 then H4 end)
,H5=max(case sid when 1 then H5 end)
'+@s+'
from(
select *,sid=(
select count(*) from #t
where H1=a.H1 and H2=a.H2 and H3=a.H3 and id<=a.id)
from #t a
)a group by H1,H2,H3
')
drop table #t
select id=identity(int,1,1),* into #t from tb
declare @s varchar(8000),@i int
select @s='',@i=max(ii)
from(
select ii=count(*) from tb
group by H1,H2,H3
)a
while @i>1
select @s=',H4'+cast(@i as varchar)+'=max(case sid when '
+cast(@i as varchar)+' then H4 end),H5'+cast(@i as varchar)
+'=max(case sid when '+cast(@i as varchar)+' then H5 end)'+@s
,@i=@i-1
exec('select H1,H2,H3
,H4=max(case sid when 1 then H4 end)
,H5=max(case sid when 1 then H5 end)
'+@s+'
from(
select *,sid=(
select count(*) from #t
where H1=a.H1 and H2=a.H2 and H3=a.H3 and id<=a.id)
from #t a
)a group by H1,H2,H3
')
drop table #t
create table tb(H1 varchar(10),H2 varchar(10),H3 varchar(10),H4 int,H5 int)
insert tb select 'A','B','C','1','2'
union all select 'A','B','C','3','4'
union all select 'C','D','F','1','2'
union all select 'C','D','E','3','4'
go--查询处理(因为不知道表中是否有主键,所以用临时表处理)
select id=identity(int,1,1),* into #t from tb
declare @s varchar(8000),@i int
select @s='',@i=max(ii)
from(
select ii=count(*) from tb
group by H1,H2,H3
)a
while @i>1
select @s=',H4'+cast(@i as varchar)+'=max(case sid when '
+cast(@i as varchar)+' then H4 end),H5'+cast(@i as varchar)
+'=max(case sid when '+cast(@i as varchar)+' then H5 end)'+@s
,@i=@i-1
exec('select H1,H2,H3
,H4=max(case sid when 1 then H4 end)
,H5=max(case sid when 1 then H5 end)
'+@s+'
from(
select *,sid=(
select count(*) from #t
where H1=a.H1 and H2=a.H2 and H3=a.H3 and id<=a.id)
from #t a
)a group by H1,H2,H3
')
drop table #t
go--删除测试
drop table tb/*--测试结果H1 H2 H3 H4 H5 H42 H52
---- ---- ---- ----- ----- ----- ------
A B C 1 2 3 4
C D E 3 4 NULL NULL
C D F 1 2 NULL NULL
--*/
select id=identity(int,1,1),* into #t from tb
declare @s varchar(8000),@i int
select @s='',@i=max(ii)
from(
select ii=count(*) from tb
group by H1,H2,H3
)a
while @i>1
select @s=',H4'+cast(@i as varchar)+'=max(case sid when '
+cast(@i as varchar)+' then H4 end),H5'+cast(@i as varchar)
+'=max(case sid when '+cast(@i as varchar)+' then H5 end)'+@s
,@i=@i-1
exec('select H1,H2,H3
,H4=max(case sid when 1 then H4 end)
,H5=max(case sid when 1 then H5 end)
'+@s+'
from(
select *,sid=(
select count(*) from #t
where H1=a.H1 and H2=a.H2 and H3=a.H3 and id<=a.id)
from #t a
)a group by H1,H2,H3
order by max(id)
')
drop table #t