structure revisited. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

structure revisited.

Author

Topic

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2012-08-08 : 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 15:22:19

do you've master table for cities? is CityID having a foreign key relationship to such a table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2012-08-11 : 01:58:03

We have a city table and both cityid and parent id from same table i.e. city table.
This is something similar to employee table where both manager and his assitant id are stored in same table and there hierachy is maintained in another table.

scoo

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources