Tag : recursive cte


Get hierarchical data in order 2

In this post, we are going to learn how we can get hierarchical data in order. We are dealing with a user defined hierarchical data which can have “n” number of siblings and their siblings can also have “n” number of siblings in turn.

For demo purpose, we have a Product_Master table, in which a product can be marked as a parent of another product, and that child product can also be marked as a parent of another product, such kind of hierarchy is known as user defined hierarchy. As any product can be marked as another product’s parent, we can have any number of nested products in the hierarchy. We have to retrieve all these products with their associated parent in ordered way.

Ordered hierarchical data from above user defined hierarchy, can be achieved with a recursive common table expression (CTE) or using a loop. We … More


Reverse the string with a delimiter (Recursive CTE) 1

In this article, we are going to learn how we can “Reverse string in sql server” with some specified delimiter e.g, based on space or comma or tilde or semi-colon etc;

If you will input “A B C”, the output should be as “C B A”

Solution:

Lets assume space as a delimiter in this example and now to achieve our goal, we will use a recursive CTE(Common table expression). Just have a look on below code and then we will discuss in detail;

Detail:-

In this demo, i have used a common table expression with recursion. A recursive CTE consists of three parts;

  1. Anchor part – Part of CTE above UNION ALL
  2. Recursive part – Part below UNION ALL which will be called recursively
  3. Condition to break – Condition to break the recursion where certain condition met

In sample code, i have used a trick that in anchor … More


Convert a comma separated list into table 4

In this post, we are going to learn “how we can convert a comma separated list into a table using a recursive CTE”. Below is the code:

Why OPTION MAXRECURSION?

Hint OPTION(MAXRECURSION 32767) is a query hint which instructs to compiler to iterate 32767 times. Default, max recursion of a loop in SQL is 100 and in case we will not put this hint at the end, any string which requires more loop than 100 will raise an error.
To avoid the error we have used OPTION(MAXRECURSION 32767).

Thanks for reading.… More