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,




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


FROM Categories

WHERE ParentID = 0


SELECT e.CategoriesID,



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




FROM CategoriesView

ORDER BY TreeOrder



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


  • 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…

2 useful hierarchical functions


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: