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 HierarchyThe 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 HierarchyThe only factor that would remain would be the sort order. Developers
can create their own logic to order the result set.
