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
UNION ALL
/* recursive member */
SELECT
PreviousValue + 1,
2 * (PreviousValue + 1)
FROM Counter
/* termination check */
WHERE PreviousValue < 10
)
/* invocation */
SELECT
*
FROM Counter;
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 😀
Show two columns. In the first column (column name PreviousValue), show the numbers from one to ten. In the second column (column name PreviousSum), show the total sum of all numbers up till the current number.
In the second column, always add the PreviousValue + 1 to the PreviousSum in the recursive member.
