CTE -Common Table Expressions for Hierarchical Data

For Hierarchical data using reference of id of its own Table, Common Table  Expression (CTE) is very useful.

syntax:

with subtree as
(
-- first statement gives SubTree which is used in Second Statement After Union all for Recursion.
 select column1, column2 from table

union all

select R.Column1, R.Column2 from table as R
inner join subtree T
on R.ParentID= T.Column1
)

select * from subtree

here, union calls the data from "subtree" recursively to get the hierarchical data uder each head.

example:Below example illustrate how cte can be used in user defined function to get the hierarchical structure of data.

GO
/****** Object:  UserDefinedFunction [dbo].[GetTreeWithCte]    Script Date: 10/03/2012 13:04:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create function [dbo].[GetTreeWithCte] (@BudgetHeadMasterID int, @maxDepth int)
returns table   ----Datatype
as
return (
    with SubTree as
    (
---first statement gives SubTree which is used in Second Statement After Union all for Recursion
        select BudgetHeadMasterID ,ParenID ,BudgetHeadMasterName , 0 as nodeDistance
        from tbl_BudgetHeadMaster 
        where BudgetHeadMasterID = @BudgetHeadMasterID
        union all
        select R.BudgetHeadMasterID, R.ParenID, R.BudgetHeadMasterName, T.nodeDistance+1
        from tbl_BudgetHeadMaster R
        inner join SubTree T
            on (@maxDepth<0 or T.nodeDistance<@maxDepth)
                and R.ParenID = T.BudgetHeadMasterID
    )
    select * from SubTree
);