Hi,
Here is the sample test data :
Declare @temp table
(id float,
pid float null,
name varchar(254) null,
treeid int null
)
Insert @temp
Select 2959505,null,'parent',2959505
union
Select 2959510,2959505,'child15',2959505
union
Select 2959509,2959505,'child14',2959505
union
Select 2959508,2959505,'child13',2959505
union
Select 3244020,2959508,'child21',2959505
union
Select 2959507,2959505,'child12',2959505
union
Select 2959506,2959505,'child11',2959505
Select * from @temp
When the above query is executed, I get the following result set:
id pid name treeid
2959505 NULL parent 2959505
2959506 2959505 child11 2959505
2959507 2959505 child12 2959505
2959508 2959505 child13 2959505
2959509 2959505 child14 2959505
2959510 2959505 child15 2959505
3244020 2959508 child21 2959505
As you can see from the above result set,
2959505 is the parent for 2959506,2959507,2959508,2959509,2959510.
and again 2959508 becomes parent for 3244020.
and also there may be chance that 3444020 becomes parent for some other new id.
I would like my result set to look like a tree structure like in a parent child way:
2959505 parent
2959506 child11
2959507 child12
2959508 child13
3244020 child21
2959509 child14
2959510 child15
Any ideas of how to achieve the above result set or something which can look similar
Thanks,
Raaj