Retrieving Query Result in a Tree Structure in MS SQL SERVER using Common Table Expression (CTE)

A common table expression or "CTE" is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table. A derived table exists only for the duration of the query and is not stored as an object. But the problem with derived tables is the lack of self referencing. Here is where the importance of CTE comes in. A CTE can be self-referencing and it can be referenced multiple times in a query. Here we discuss about the recursive nature of CTE i.e to create a recursive query. Returning hierarchical data is a common use of recursive queries i.e for example suppose we want to create a general ledger(Example query is given at the end of this section). For now lets have a look at the recursive CTE. The following are the components of a recursive CTE:

WITH cte_name [(col1,col2,......,coln)] (Here the column names are optional)
AS
(
cte_query (Anchor member defined here)
UNION ALL
cte_query(recursive member referencing cte_name here)

)
select statement to retrieve the result

A recursive CTE consists of three elements:

1). Invocation of the routine
This part consist of one or more cte_query definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. This query definitions are referred to as anchor elements because they form the base resultset of CTE.

2)Recursive invocation of routine
The recursive invocation includes one or more CTE_query_definitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.

3)Termination check.
The termination check is implicit i.e recursion stops when no rows are returned from the previous invocation.

The semantics behind CTE is as follows:

a)Splitting the CTE expression into anchor and recursive members.
b) Then we run the anchor member creating the first invocation or base result, say BR0 .
c) Now we run the recursive member with BRi(where i=0 to n) as input and BRi+1 as output.
d) Repeat step c until an empty set is returned.
e)Return the resultset this is BR0 UNION ALL BRn

Example Query

WITH MyLedger(BranchID, AccParentID, AccID) AS
(
SELECT DISTINCT BranchID,AccParentID, AccID
FROM GeneralLedger
WHERE ActiveFlag='Y'
UNION ALL
SELECT e.BranchID,e.AccParentID, e.AccID
FROM GeneralLedger e
INNER JOIN MyLedger d
ON e.AccParentID = d.AccID AND E.BranchID=d.BranchID AND ActiveFlag='Y'
)

SELECT * FROM MyLedger

BranchID-denotes the branch.
AccParentID- denotes the parent group here.
AccID-denotes the account id.
The above result displays a General Ledger in a Tree Structure branch wise. Here we form our base result from the table GeneralLedger.

Happy Programming...... Enjoy!!!!!!!!!!!!!!!!!!!!!!!!!