Building a TreeView ResultSet with SQL Server 2005
page 4 of 5
by Ameet Phadnis
Feedback
Average Rating: 
Views (Total / Last 10 Days): 42934/ 96

Writing your Query

As explained above, we need to create our first anchor query.  The query will be for the root level elements which are going to be the ones that do not have any Parent Department.  The query will look like the listing below.

Listing 2

SELECT DepartmentID, DepartmentName, ParentDepartmentID FROM tblDepartments

We need to change the query to include the HeirarchyLevel.  For this example, I will call it HLevel. So, now our query will look like the following.

Listing 3

SELECT DepartmentID, DepartmentName, ParentDepartmentID, 0 as HLevel FROM tblDepartments

 Now define the CTE structure using the above defined columns.

Listing 4

WITH Hierarchy(DepartmentID, DepartmentName, ParentDepartmentID, HLevel)

The above is the definition of the CTE.  Now we need to define the AS clause for the CTE.

Listing 5

WITH Hierarchy(DepartmentID, DepartmentName, ParentDepartmentID, HLevel)
AS
(
SELECT DepartmentID, DepartmentName, ParentDepartmentID, 0 as HLevel FROM tblDepartments
UNION ALL
<Recursive Query>
)

The recursive query will add 1 to the parent Level.  The recursive query will look like Listing 6.

Listing 6

SELECT SubDepartment.DepartmentID, 
SubDepartment.DepartmentName,
 SubDepartment.ParentDepartmentID, 
HLevel + 1
    FROM tblDepartments SubDepartment
        INNER JOIN Hierarchy ParentDepartment
        ON SubDepartment.ParentDepartmentID = ParentDepartment.DepartmentID

The final query will be:

Listing 7

WITH Hierarchy(DepartmentID, DepartmentName, ParentDepartmentID, HLevel)
AS
(
SELECT DepartmentID, DepartmentName, ParentDepartmentID, 0 as HLevel FROM tblDepartments
UNION ALL
SELECT SubDepartment.DepartmentID,
 SubDepartment.DepartmentName,
 SubDepartment.ParentDepartmentID,
 HLevel + 1
    FROM tblDepartments SubDepartment
        INNER JOIN Hierarchy ParentDepartment
        ON SubDepartment.ParentDepartmentID = ParentDepartment.DepartmentID

)

Once the CTE structure is defined and we have the result set, we need to display the complete result set.

We need to add the following select statement to display the result set.

Listing 8

SELECT      DepartmentID, 
            DepartmentName = Replicate('.', HLevel) + DepartmentName, 
            ParentDepartmentID,
            HLevel
FROM  Hierarchy

The Replicate function makes sure that you add "." based on the level.

In short, what I explained in my previous article for SQL Server 2000 can be done using the following.

Listing 9

WITH Hierarchy(DepartmentID, DepartmentName, ParentDepartmentID, HLevel)
AS
(
SELECT DepartmentID, DepartmentName, ParentDepartmentID, 0 as HLevel FROM tblDepartments
UNION ALL
SELECT SubDepartment.DepartmentID
, SubDepartment.DepartmentName,
 SubDepartment.ParentDepartmentID,
 HLevel + 1
    FROM tblDepartments SubDepartment
        INNER JOIN Hierarchy ParentDepartment
        ON SubDepartment.ParentDepartmentID = ParentDepartment.DepartmentID )
SELECT      DepartmentID, 
            DepartmentName = Replicate('.', HLevel) + DepartmentName, 
            ParentDepartmentID,
            HLevel
FROM  Hierarchy

The only factor that would remain would be the sort order.  Developers can create their own logic to order the result set.


View Entire Article

User Comments

Title: Hierarchical structure   
Name: Venkat
Date: 2008-12-22 8:08:36 AM
Comment:
Before getting this site
I was much confused to solve query in Hierarchical.
its very helpful to all...
thanks....
Title: Works like a dream!   
Name: Per
Date: 2008-12-20 1:51:09 AM
Comment:
Just make sure you have an appropriate WHERE clause for your first 'anchor query'. In the example above I gather it should be:
'... 0 as HLevel FROM tblDepartments WHERE ParentDepartmentID is null'
instead of:
'... 0 as HLevel FROM tblDepartments'
Title: Thanks   
Name: Raheel
Date: 2008-05-11 11:50:21 PM
Comment:
Nieece ... For a moment I though I should leave IT ...Thanks Ameet ..and Thanks Google
Title: SQL/XML format string   
Name: Jayeff_Land
Date: 2008-04-08 1:57:53 PM
Comment:
I have found my own answer on the web. If anybody is interested : http://blogs.conchango.com/christianwade/archive/2006/09/20/SQL-Server-Standard-_2D00_-Recursive-Hierarchies-to-XML.aspx
Title: SQL/XML format string   
Name: Jayeff_Land
Date: 2008-04-08 1:55:04 PM
Comment:
I have been seaching for a really good example for that! It was really simple and helpful.
Is it possible to transfer the result in a XML format string?
Title: Very Helpful   
Name: Felipe Bossolani
Date: 2007-03-20 11:08:18 AM
Comment:
Thanks,
Sounds Perfect and Works PERFECT!
=)
Title: Cannot get the subcategories under the main ones   
Name: Jovanky De Los Santos
Date: 2007-03-16 10:35:03 AM
Comment:
My Table Structure

Category_ID Number
Parent_ID Number <----Category_ID reports to this colum
Category_Name Varchar....

MY QUERY <---I replaced the query above with my data
=============================
WITH Hierarchy(Category_ID, Category_Name, Parent_ID, HLevel)
AS
(
SELECT Category_ID, Category_Name, Parent_ID, 0 as HLevel FROM Dir_Categories
UNION ALL
SELECT SubCategory.Category_ID
, SubCategory.Category_Name,
SubCategory.Parent_ID,
HLevel + 1
FROM Dir_Categories SubCategory
INNER JOIN Hierarchy ParentCategory
ON SubCategory.Parent_ID = ParentCategory.Category_ID )
SELECT Category_ID,
Category_Name = Replicate('__', HLevel) + Category_Name,
Parent_ID,
HLevel
FROM Hierarchy

My OUTPUT============

All the categories under reporting to Parent_ID 0 or continuous, then the ones reporting to 1 and so fourth. Subcategories are not showing within their main categories. I AM GOING NUTS WITH THIS.

Can you help me please?
Title: What about employees   
Name: Rodney
Date: 2007-01-25 8:58:00 AM
Comment:
Hey all,
How would you include the actual employees and where they would appear in the tree? Would you need another table or could I add fields to the table presented in this article?

thanks,
Rodney
Title: Building a TreeView ResultSet with SQL Server 2005   
Name: Pavel Kubalak
Date: 2007-01-08 12:12:05 PM
Comment:
Very helpful.
Big thanks
Title: Building a TreeView ResultSet with SQL Server 2005   
Name: Piers Lawson
Date: 2006-10-27 12:23:15 PM
Comment:
Whilst this new functionality lets you get your result via recurssion, a better solution (that is more efficient, doesn't have the parent NULL issue and works in any database) is to store your heirarchy using Nested Sets as advocated by Joe Celko. One simple query returns all the information you need.
Title: mr   
Name: Pierre Bakker
Date: 2006-10-24 3:42:30 AM
Comment:
Hello,

I think you missed an IS NULL on the parentID? If I execute the query in your example it returns all records on all HLevels. I think you need to add a WHERE Clause to the query definition
SELECT
DepartmentID,
DepartmentName,
ParentDepartmentID,
0 as HLevel
FROM
tblDepartments
-- extra WHERE
WHERE
ParentDepartmentID is null.

This way you start at the root of the tree. Am I right?

grtz Pierre Bakker
Title: Building a TreeView ResultSet with SQL Server 2005   
Name: Sanket Terdal
Date: 2006-10-04 8:55:44 AM
Comment:
Very useful article.






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-20 7:25:06 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search