|
scottichrosaviakosmos
Yak Posting Veteran
Russia
66 Posts |
 Posted - 08/08/2012 : 15:00:09
|
create table #abc(insertid int,cityid int,parentcityid int,cityname varchar(50),parentname varchar(50))
insert into #abc
select 1,10,11,'A','B'
union
select 1,11,12,'B','C'
union
select 1,12,13,'C','D'
union
select 2,10,11,'A','B'
union
select 2,11,13,'B','D'
union
select 2,12,11,'C','A'
Output:
Insertid Cityid Parentid cityname parentname
1 10 11 A B
1 11 12 B C
1 12 13 C D
2 10 11 A B
2 11 13 B D
2 12 11 C A
Where ever there is a change in parent for same child for different insertid then ther should be a new id assign to old id. Eg: in table for insertid 1 cityid 11 had parent c(id=12) but for insertid 2 cityid 11 has parented as13 . So the desired output should be like:
Insertid Cityid Parentid cityname parentname
1 10 11 A B
1 11 12 B C
1 12 13 C D
2 10 11 A B
2 14 13 B D
2 15 11 C A
scoo |
|
structure revisited.
