SQLTeam.com | Weblogs | Forums

Help needed with SQL Query in SQL 2005 for the following:



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
Select 2959510,2959505,'child15',2959505
Select 2959509,2959505,'child14',2959505
Select 2959508,2959505,'child13',2959505
Select 3244020,2959508,'child21',2959505
Select 2959507,2959505,'child12',2959505
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


;with cte as
	select id, cast('Parent' as varchar(64)) as [Parent],
	1 as level
	from @temp where pid is null

	union all

	select t.id,
		cast ('child' + cast(10*c.level + row_number() over (order by pid) as varchar(32)) as varchar(64)),
		 c.level + 1
		cte c
		inner join @temp t on
			t.pid = c.id
select id, Parent from cte;