August 14, 2015

One of the most benefit of CTE (Common Table Expressions) is that we can create
recursive queries with them.

Recursive query is the efficient way to display hierarchy setup (Parent child relation
ship in the same table). e.g. Grand Parent, Parent, Child. This is common requirement.
To display the parent child relationship in efficient manner, we can use Recursive
CTE. Let me explain it by example.

This is not the way that we can understand the hierarchy. Recursive query will help
us to display result like this:

This is much efficient way to list out, as we understand that how the hierarchy
is being maintained. Recursive CTE helps us to generate this kind efficient output.
Let me share the t-sql script which generates the output which we are looking for: