I remember the first time that I discovered the amazing feature called Common Table Expression (CTE), it was during my first read of Itzik Ben-Gan book (T-SQL Querying), maybe one of the most interesting feature together with Windows Functions.  I know, I am a declared fan of CTE, but we can start defining a CTE as a sort of temporary result set and which exists only during the lifetime of the query.

In this short blog I would like to explain how we can utilize a CTE in a recursive way, it means that it can self-referencing, or even referenced multiple times in the same query.  This capability of self-referencing opens a door of opportunities and scenarios for implementing a recursive query.  I would like to mention a specific case that I had from customer requirement, it consisted in the implementation of a mechanism for “generating” a row a specific number of times based on a column which indicates us how many times the rows have to be repeated.

You have the next input information which has been adapted to be more didactic and easy to try:

What it is the desired output, basically on the base of the number of repetition contained in the column NumberOfRepetition it should generate the CombinationId and the percentageValue rows the same number of times, for example for the CombinationId = 2 there are four repetitions which must have the PercentageValue equal to 350000.

       For instance, the expected result for the first two rows should be:

I would like to define some key ideas about recursive CTE, the main structure of a recursive CTE is formed by the next elements: an anchor member, recursive member, and terminator.  The next code shows a simple example:

WITH   MyCTE
AS     (SELECT 1 AS n -- anchor member
        UNION ALL
        SELECT n + 1 -- recursive member
        FROM   MyCTE
        WHERE  n < 100 -- terminator
       )
SELECT n
FROM   MyCTE;

We can define the anchor member as a query which is self-contained and executed only once, in the previous example was the initial point, later, we have the UNION ALL that indicate us the union of the anchor member with the result of the recursive query (the second section after UNION ALL).  The key part which allows the recursivity is the reference to CTE name, in this case, MyCTE.

It is very important to notice that the reference of CTE name is basically the inmediate previous result, think in a process which is filling a kind of working table and consolidating the dataset, thus the recursive member is working aligns with the terminator for avoiding an infinite loop and allow it stops when there will not have more rows to processing, I mean, rows which not pass the filter defined by the terminator part.

The next code is built from scratch and you can see the logic behind it.


-- Set up the initial table for the testing purpose

DECLARE @CombinationSeed AS TABLE
(
[CombinationId] INT ,
[RankValue] INT ,
[PercentageValue] INT ,
[NumberOfRepetition] INT
);

-- Populating the test table
INSERT INTO @CombinationSeed ( [CombinationId] ,
[RankValue] ,
[PercentageValue] ,
[NumberOfRepetition]
)
VALUES ( 1, 1, 650000, 1 ) ,
( 2, 2, 350000, 3 ) ,
( 3, 1, 500000, 1 ) ,
( 4, 2, 300000, 4 ) ,
( 5, 3, 200000, 5 );

-- Implementing the recursive CTE approach
WITH MyCTE_Recursive
AS ( SELECT *
FROM @CombinationSeed
UNION ALL
SELECT MCR.CombinationId ,
MCR.RankValue ,
MCR.PercentageValue ,
MCR.NumberOfRepetition - 1 -- This operation is the key for filter it
FROM MyCTE_Recursive AS MCR
INNER JOIN @CombinationSeed AS MPP ON MCR.CombinationId = MPP.CombinationId
WHERE MCR.NumberOfRepetition>1
)
SELECT MyCTE_Recursive.CombinationId ,
MyCTE_Recursive.PercentageValue
FROM MyCTE_Recursive
ORDER BY MyCTE_Recursive.CombinationId;

You can run and easily confirm how much useful this small trick can be for different scenarios.

More useful information about CTE and CTE Recursive can be found in the next links:

http://www.itprotoday.com/software-development/ctes-multiple-recursive-members

http://www.itprotoday.com/software-development/ctes-multiple-recursive-members-part-2