--生成测试数据
create table 表一(
id int,
name1 varchar(10),
name2 varchar(10),
name3 varchar(10))insert into 表一 select 1,'a1','a2','a3'
insert into 表一 select 2,'b1','b2','b3'
insert into 表一 select 3,'c1','c2','c3'create table 表二(
id int,
fk_Id int,
s1 varchar(10),
s2 varchar(10),
s3 varchar(10))insert into 表二 select 1,1,'t1','t2','t3'
insert into 表二 select 2,1,'y1','y2','y3'
insert into 表二 select 3,1,'z1','z2','z3'
insert into 表二 select 4,2,'g1','g2','g3'
insert into 表二 select 5,2,'h1','h2','h3'
insert into 表二 select 6,3,'u1','u2','u3'--执行查询
select identity(int,1,1) as nid,id,fk_Id,s1,s2,s3 into temp from 表二 order by fk_Id,idupdate a
set id = (a.nid - b.nid +1)
from
temp a,
(select fk_Id,min(nid) as nid from temp group by fk_Id) b
where
a.fk_Id = b.fk_Iddeclare @s varchar(8000)
declare @i int
select @i = max(id) from temp
set @s = ''while @i > 0
begin
select @s = ',[s1_'+rtrim(@i)+'] = max(case when b.id = '+rtrim(@i)+' then b.s1 end)
,[s2_'+rtrim(@i)+'] = max(case when b.id = '+rtrim(@i)+' then b.s3 end)
,[s3_'+rtrim(@i)+'] = max(case when b.id = '+rtrim(@i)+' then b.s2 end)' + @s
set @i = @i-1
endset @s = 'select a.*' + @s + ' from 表一 a,temp b where a.id = b.fk_id group by a.id,a.name1,a.name2,a.name3'
exec(@s)
--删除测试数据
drop table 表一
drop table 表二
drop table temp
--执行结果
/*
id name1 name1 name1 s1_1 s2_1 s3_1 s1_2 s2_2 s3_2 s1_3 s2_3 s3_3
--------------------------------------------------------------------------
1 a1 a2 a3 t1 t2 t3 y1 y2 y3 z1 z2 z3
2 b1 b2 b3 g1 g2 g3 h1 h2 h3
3 c1 c2 c3 u1 u2 u3
*/
create table 表一(
id int,
name1 varchar(10),
name2 varchar(10),
name3 varchar(10))insert into 表一 select 1,'a1','a2','a3'
insert into 表一 select 2,'b1','b2','b3'
insert into 表一 select 3,'c1','c2','c3'create table 表二(
id int,
fk_Id int,
s1 varchar(10),
s2 varchar(10),
s3 varchar(10))insert into 表二 select 1,1,'t1','t2','t3'
insert into 表二 select 2,1,'y1','y2','y3'
insert into 表二 select 3,1,'z1','z2','z3'
insert into 表二 select 4,2,'g1','g2','g3'
insert into 表二 select 5,2,'h1','h2','h3'
insert into 表二 select 6,3,'u1','u2','u3'--执行查询
select identity(int,1,1) as nid,id,fk_Id,s1,s2,s3 into temp from 表二 order by fk_Id,idupdate a
set id = (a.nid - b.nid +1)
from
temp a,
(select fk_Id,min(nid) as nid from temp group by fk_Id) b
where
a.fk_Id = b.fk_Iddeclare @s varchar(8000)
declare @i int
select @i = max(id) from temp
set @s = ''while @i > 0
begin
select @s = ',[s1_'+rtrim(@i)+'] = max(case when b.id = '+rtrim(@i)+' then b.s1 end)
,[s2_'+rtrim(@i)+'] = max(case when b.id = '+rtrim(@i)+' then b.s3 end)
,[s3_'+rtrim(@i)+'] = max(case when b.id = '+rtrim(@i)+' then b.s2 end)' + @s
set @i = @i-1
endset @s = 'select a.*' + @s + ' from 表一 a,temp b where a.id = b.fk_id group by a.id,a.name1,a.name2,a.name3'
exec(@s)
--删除测试数据
drop table 表一
drop table 表二
drop table temp
--执行结果
/*
id name1 name1 name1 s1_1 s2_1 s3_1 s1_2 s2_2 s3_2 s1_3 s2_3 s3_3
--------------------------------------------------------------------------
1 a1 a2 a3 t1 t2 t3 y1 y2 y3 z1 z2 z3
2 b1 b2 b3 g1 g2 g3 h1 h2 h3
3 c1 c2 c3 u1 u2 u3
*/
create table 表一(
id int,
name1 varchar(10),
name2 varchar(10),
name3 varchar(10))insert into 表一 select 1,'a1','a2','a3'
insert into 表一 select 2,'b1','b2','b3'
insert into 表一 select 3,'c1','c2','c3'create table 表二(
id int,
fk_Id int,
s1 varchar(10),
s2 varchar(10),
s3 varchar(10))insert into 表二 select 1,1,'t1','t2','t3'
insert into 表二 select 2,1,'y1','y2','y3'
insert into 表二 select 3,1,'z1','z2','z3'
insert into 表二 select 4,2,'g1','g2','g3'
insert into 表二 select 5,2,'h1','h2','h3'
insert into 表二 select 6,3,'u1','u2','u3'--执行查询
select identity(int,1,1) as nid,id,fk_Id,s1,s2,s3 into temp from 表二 order by fk_Id,idupdate a
set id = (a.nid - b.nid +1)
from
temp a,
(select fk_Id,min(nid) as nid from temp group by fk_Id) b
where
a.fk_Id = b.fk_Iddeclare @s varchar(8000)
declare @i int
select @i = max(id) from temp
set @s = ''while @i > 0
begin
select @s = ',[s1_'+rtrim(@i)+'] = max(case when b.id = '+rtrim(@i)+' then b.s1 end)
,[s2_'+rtrim(@i)+'] = max(case when b.id = '+rtrim(@i)+' then b.s2 end)
,[s3_'+rtrim(@i)+'] = max(case when b.id = '+rtrim(@i)+' then b.s3 end)' + @s
set @i = @i-1
endset @s = 'select a.*' + @s + ' from 表一 a,temp b where a.id = b.fk_id group by a.id,a.name1,a.name2,a.name3'
exec(@s)drop table 表一
drop table 表二
drop table temp
---------------------------------------------------------------
--生成测试数据
create table 表一(
id int,
name1 varchar(10),
name2 varchar(10),
name3 varchar(10))insert into 表一 select 1,'a1','a2','a3'
insert into 表一 select 2,'b1','b2','b3'
insert into 表一 select 3,'c1','c2','c3'create table 表二(
id int,
fk_Id int,
s1 varchar(10),
s2 varchar(10),
s3 varchar(10))insert into 表二 select 1,1,'t1','t2','t3'
insert into 表二 select 2,1,'y1','y2','y3'
insert into 表二 select 3,1,'z1','z2','z3'
insert into 表二 select 4,2,'g1','g2','g3'
insert into 表二 select 5,2,'h1','h2','h3'
insert into 表二 select 6,3,'u1','u2','u3'--执行查询
select identity(int,1,1) as nid,0 as id1,id,fk_Id,s1,s2,s3 into temp from 表二 order by fk_Id,idupdate a
set id1 = (a.nid - b.nid +1)
from
temp a,
(select fk_Id,min(nid) as nid from temp group by fk_Id) b
where
a.fk_Id = b.fk_Id
declare @s varchar(8000)
declare @i int
select @i = max(id1) from temp
set @s = ''while @i > 0
begin
select @s = ',[s1_'+rtrim(@i)+'] = max(case when b.id1 = '+rtrim(@i)+' then b.s1 end)
,[s2_'+rtrim(@i)+'] = max(case when b.id1 = '+rtrim(@i)+' then b.s2 end)
,[s3_'+rtrim(@i)+'] = max(case when b.id1 = '+rtrim(@i)+' then b.s3 end)' + @s
set @i = @i-1
endset @s = 'select a.*' + @s + ' from 表一 a,temp b where a.id = b.fk_id group by a.id,a.name1,a.name2,a.name3'
exec(@s)drop table 表一
drop table 表二
drop table temp