Great! Just as you can have multiple columns in your CTE, you can also have multiple CTEs, some of which may be recursive, others that may not. Take a look at the example below.
WITH Prime(X) AS (
SELECT 2
UNION
SELECT 3
UNION
SELECT 5
UNION
SELECT 7
UNION
SELECT 11
),
Total (Multiply, Prime, Score) AS (
SELECT
0,
0,
0
UNION ALL
SELECT
Multiply + 1,
X,
X * (Multiply + 1)
FROM Prime,
Total
WHERE Multiply < 3
)
SELECT DISTINCT
*
FROM Total
ORDER BY Multiply ASC,
Prime ASC;
In the example, we first create a list of the first 5 prime numbers, and then we create a recursive CTE that calculates the multiples of these prime numbers.
After running and analyzing the query, you may see that the CTE Total returns some duplicates. You may wonder why we haven't used UNION. The problem is that SQL Server doesn't accept a UNION between the anchor member and the recursive member in a CTE. The workaround for this problem is to SELECT DISTINCT in the invocation part.
Look at the template. There is already a single CTE named Digit defined for you. Your task is to complete the Numbers CTE so that it generates all possible numbers consisting of those numbers that have at most 5 digits.
The anchor member should be a digit from the table Digit.
The recursive member should use the number you have so far and a new digit from Digit. Use the formula 10 * W + X to add a new digit.
Remember about the termination check – make sure that the number isn't longer than 5 digits.
