 Print
 Add To Favorites
 Email To Friend
 Rate This Article
|
Building a TreeView ResultSet with SQL Server 2005
|
Published:
02 Oct 2006
|
Abstract
This article is an upgrade of my previous article on Building Tree View Resultset. It explains how we can create the Tree View Resultset with SQL 2005 with just one SQL Statement. |
 |
by Ameet Phadnis
Feedback
|
Average Rating: 
Views (Total / Last 10 Days):
29229/
85
|
|
|
|
| Introduction |
In a corporate environment we usually face coding tasks to
display results in Treeview. The tasks may include displaying corporate
structure or in programs displaying the menu structure. Around 5 to 6 years ago
when I was tasked with a similar kind of assignment, my manager wanted me to
get the results through the SQL Stored Procedure and then the web front end
would do the rest. I came up with a way to create the tree hierarchy in SQL
Server 2000. It was very complex and hard to understand. We had no idea how
much SQL Server 2005 would simplify our lives. I have explained the procedure
in my previous article. You can view the article by clicking here.
Before I give you the SQL Script, I would like to explain
some of the new features introduced in SQL Server 2005.
|
|
|
|
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.
|
|
|
|
|
|
