CTE with multiple anchors

Common Table Expression (CTE) offers a lot of options on how to play with it. One of them is using recursion with an anchor which is in the standard scenario one row. But we can have a more complex anchor: instead of one row, it can be i.e. union on more rows creating new anchor dataset.

How that works is presented in this simple example:

;WITH cte(Value)
AS
(
    SELECT CONVERT(VARCHAR(1000),'Anchor1_') 
    UNION ALL
    SELECT CONVERT(VARCHAR(1000),'Anchor2_') 
    UNION ALL
    SELECT CONVERT(VARCHAR(1000), Value + 'x') 
    FROM cte
    WHERE LEN(Value) < 15    
)
SELECT *
FROM cte
ORDER BY LEN(Value)

We have created two anchor rows: Anchor1_ and Anchor2_. Then we are running recursive iteration over it adding an ‘x’ in every step until the total length of the string is 15. Instead of UNION ALL we can use any kind of option on how to create a multirow anchor dataset like VALUES() or standard SELECT statement.