Retreiving Tree-Structure data from a Table throug

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

General SQL Server Forums

New to SQL Server Programming

Retreiving Tree-Structure data from a Table throug

Author

Topic

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2007-04-07 : 15:21:47

Hi all
I have a Table with Following structure ( a Tree Structure )

PK Parent Level Code
--- -------- ------- ------
1 0 0 100
2 1 1 101
3 1 1 102
4 2 2 103
5 3 2 104
6 4 3 105

The same as following Tree as you can see
1__
| 2__
| 4__
| 6
| __
3__
5

I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that
Could any one help me.?

PK Parent Level Value First-Parent' Code 2nd-Parent's Code 3rd-Parent's Code
---- -------- ------ ------- --------------------- ---------------------- -----------------------
1 0 0 100 NULL NULL NULL
2 1 1 101 100 NULL NULL
3 1 1 102 100 NULL NULL
4 2 2 103 101 100 NULL
5 3 2 104 102 100 NULL
6 4 3 105 103 101 100

Any help greatly would be appreciated.
Kind Regards.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-07 : 18:45:36

[code]
declare @table table
(
PK int,
Parent int,
Level int,
Code int
)
insert into @table
select 1, 0, 0, 100 union all
select 2, 1, 1, 101 union all
select 3, 1, 1, 102 union all
select 4, 2, 2, 103 union all
select 5, 3, 2, 104 union all
select 6, 4, 3, 105

select t.PK, t.Parent, t.Level, Value = t.Code,
[P1 Code] = max(p1.Code),
[P2 Code] = max(p2.Code),
[P3 Code] = max(p3.Code)
from @table t
left join @table p1
on t.Parent = p1.PK
left join @table p2
on p1.Parent = p2.PK
left join @table p3
on p2.Parent = p3.PK
group by t.PK, t.Parent, t.Level, t.Code
[/code]

ZarrinPour
Yak Posting Veteran

66 Posts

Posted - 2007-04-07 : 22:32:41

quote:

Originally posted by khtan


declare @table table
(
	PK 	int,
	Parent 	int,
	Level 	int,
	Code	int
)
insert into @table
select	1, 0, 0, 100	union all
select	2, 1, 1, 101	union all
select	3, 1, 1, 102	union all
select	4, 2, 2, 103	union all
select	5, 3, 2, 104	union all
select	6, 4, 3, 105

select	t.PK, t.Parent, t.Level, Value = t.Code, 
	[P1 Code] = max(p1.Code), 
	[P2 Code] = max(p2.Code), 
	[P3 Code] = max(p3.Code)
from	@table t
left join @table p1
on	t.Parent	= p1.PK
left join @table p2
on	p1.Parent	= p2.PK
left join @table p3
on	p2.Parent	= p3.PK
group by t.PK, t.Parent, t.Level, t.Code

Hi khtan
I lack the word with which to express my thanks.

Thanks a million.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources