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.