Need help for family tree extraction from table

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)

Need help for family tree extraction from table

Author

Topic

usman400
Starting Member

1 Post

Posted - 2011-11-03 : 05:05:23

Hi
I need help with recursive queries or any solution to my situation.
I want to show family tree in rows and columns
I have following data in my table,

(F = Father ID)
(M= Mother ID)
IdNo F M
0 4 5
14 16 17
15 16 17
16 0 1
17 2 3

I want to know how I can get the query (perhaps Union query and/or CTE etc)
To show the results as follows:

IdNo F M FF FM MF MM FFF FFM
0 4 5
14 16 17 0 1 2 3 4 5
15 16 17 0 1 2 3 4 5
16 0 1 4 5
17 2 3

The base of the result is the hierarchy which can be understood by following image:

<img src='http://img97.imageshack.us/img97/8193/unledjd.jpg'/>

Usman Waheed

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-11-03 : 05:20:50

Look up RECURSIVE CTE

http://msdn.microsoft.com/en-us/library/ms186243.aspx

Also, Peso gives an example here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=130605&SearchTerms=heirarchy

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 05:25:03

make use of recursive ctes

http://msdn.microsoft.com/en-us/library/ms186243.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources