Of course, you can have more than one column in your recursive CTE. Do remember, though, that the anchor member and the recursive member must have the same number of columns – that's an essential condition for any query with
UNION ALL. Take a look:
WITH Counter (PreviousValue, PreviousDouble) AS (
/* anchor member */
SELECT 1, 2
/* recursive member */
PreviousValue + 1,
2 * (PreviousValue + 1)
/* termination check */
WHERE PreviousValue < 10
/* invocation */
In the query above, we show the numbers from 1 to 10 , plus each respective number multiplied by two.
In the recursive member, we added
2 * (PreviousValue + 1) as the second column. Because the number of columns must be the same in the anchor and the recursive members, we had to add a second column in the anchor member as well (which is just the number 2, that is 1 multiplied by 2).
Naturally, we also added the second column to the list of CTE columns:
WITH Counter (PreviousValue, PreviousDouble) AS ...
If you are curious about its result, you may copy it and run it 😀