Get Project Hierarchy from Adjacency 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 2008 Forums

Transact-SQL (2008)

Get Project Hierarchy from Adjacency Table

Author

Topic

Cousboy
Starting Member

7 Posts

Posted - 2011-01-17 : 17:22:27

Hi, I'm trying to extract a project hierarchy from from an adjacency model table in SQL Sever 2008.

I have created a table which when I enter the statement:

SELECT * FROM prproject ORDER BY project ASC

I get these results:

Project---Parent
1---------NULL
1.1-------1
1.1.1-----1.1
1.1.2-----1.1
1.1.3-----1.1
2---------NULL
2.1-------2
2.1.1-----2.1
2.1.2-----2.1
2.1.3-----2.1

What I would like to do is write a select statement or stored procedure that when I specify a "Parent" project all of the hierarchy to the nth level is shown in the results. For example if I selected parent project "1" then the results would be displayed as:

Project
1.1
1.1.1
1.1.2
1.1.3

Any help will be greatly appreciated.

Many thanks

Cousboy

matty
Posting Yak Master

161 Posts

Posted - 2011-01-18 : 00:48:44

[code]
;
WITH cte(project)
AS
(
SELECT Project FROM prproject WHERE Project = '1'
UNION ALL
SELECT s.Project FROM cte c JOIN prproject s ON c.project = s.Parent

)

SELECT * FROM cte WHERE project <> '1'
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources