scottichrosaviakosmos (8/8/2012)

---

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 AB

1 11 12 BC

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 there 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

I think now the post will be clear.

Where ever there is a change in parent for same child for different insertid then there 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 .