Use CTE to retrieve hierarchical records of a self-join table in SQL Server 2005

A tired working day, I was going to turn my PC off and went home. Abruptly, I remembered there was remaining a stuff to finish. Oh, “what the hell is going on?”, a table named “Categories” and it is self-join and I had to select all records of its in terms of hierarchical style. I was confusing; also, hungry. I made a call to a friend, he is an expert in Oracle. He said in SQL Server 2005, “Common Table Expression (CTE)” should be my solution. After about 15 minutes of google, I found the T-SQL code to address my problem. It is about to show up bellow

 

WITH CategoriesView (CategoriesID, ParentID, CategoriesName, TreeOrder) AS

(

SELECT CategoriesID,

ParentID,

CategoriesName,

 

Cast(dbo.FormatString(SortOrder,5) as varchar)

 

FROM Categories

WHERE ParentID = 0

UNION ALL

SELECT e.CategoriesID,

e.ParentID,

e.CategoriesName,

Cast((dbo.FormatString(cv.TreeOrder,5) + dbo.FormatString(c.SortOrder,5)) as varchar) as TreeOrder

FROM Categories c

INNER JOIN CategoriesView cv

ON c.ParentID = cv.CategoriesID

)

 

SELECT *

FROM CategoriesView

ORDER BY TreeOrder

 

Description:

  • CategoriesID is an identity column of Int(4)
  • SortOrder is order of a Categories in its level.

Explanation:

  • We use CTE, a new feature in SQL Server 2005; So, with keyword “WITH”, we generate a view of all records in table Categories. In that view, a new column “TreeOrder” is spawned by recursively adding the TreeOrder value of parent Category to current SortOrder value.

dbo.FormatString(cv.TreeOrder,5) + dbo.FormatString(c.SortOrder,5)

 

There is another way (but a neglect one) is create a column “TreeOrder” in table Categories. Every time table Categories is updated (delete/add/update), we have to run a stored procedure to re-calculate the value of “TreeOrder”.

 

I was late for home about 1 hour, but had a great day!

Nguyen Minh Dung

One comprehensive resource is http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/recursivequer…

2 useful hierarchical functions

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: