Example of Common Table Expression (CTE) in SQL Server 2005

Giving you example for Common Table expression (CTE) to get levels with members Hierarchy

Common syntax for CTE as following

--Syntax

With CTE_Name (Column_Name_1, Column_Name_2 … Column_Name_n )
As
(
cte_Anchor_Member_Query_Definition
UNION ALL or UNION EXCEPT OR INTERSECT
Cte_Recursion_Query_Definition (Must refer to CTE_Name)
)
--TSQL which uses the CTE

Below is the example id using CTE

WITH TreeCTE(ChildId, ParentId, level )
AS
(
-- Anchor Member
select col_ChildId,col_ParentId, 0 from TBL_Members where col_ParentId is null

UNION ALL
-- Recursive Member
select t.col_ChildId,t.col_ParentId, ct.level + 1 from TBL_Members t inner join
TreeCTE ct on ( t.col_ParentId= ct.ChildId)
)

-- Using the CTE
select * from TreeCTE order by level

By executing above query you can get result as following

From above example , first is the anchor statement where we fetch for root row
then using union all with join of CTE to get level and Hierarchy of every members.

You can see the anchor statement result