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
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
Comments
Post a Comment