Interesting queries using recursive Common Table Expressions

I have just given an attempt to show how the recursive member of the common table expression (CTE) can be used.

A recursive CTE has 2 parts, the anchor member and the recursive member. The anchor member will first put in a set of rows into the CTE and the recursive member will use the newly added rows in the CTE to get a few more rows and adds it to the CTE. Now the recursive member will act upon the newly added rows again and this will go on till the recursive member does not produce any rows by acting upon the penultimate rows added to it.


The recursive member usually does a self join with the common table expression that you try to create. Well how it joins, I guess the BOL has good examples on that..
But what can we do with the CTE?

Here are a few things that you can generate using CTEs. No practical use, I guess, but interesting. I will keep adding more as and when I think of one.

1.Generate a numbers table to a max of 32767 from absolutely nothing

with numbers as
(select 1 as Num
union all
select Num + 1 from numbers where Num <10000)
select * from numbers option(Maxrecursion 10000)


2. Get the factorial of numbers from 1 to 20

with fact_tbl as
(select 1 as num,cast(1 as bigint) as fact
union all
select num+1,fact*(num + 1) from fact_tbl where num <20)
select * from fact_tbl


3. Splitting a comma separated list

declare @a varchar(100)
set @a = 'A,B,C,D,E,F,G,H,IJ,KL,MN,OP,WER,TRY,QQR';

with tbl_for_csv as
(
select left(@a + ',,',charindex(',',@a + ',,') -1)as Col,
right(@a + ',,',len(@a + ',,') - charindex(',',@a + ',,')) as Str
union all
select left(Str,charindex(',',Str) - 1)as Col,
right(Str,len(Str) - charindex(',',Str)) from tbl_for_csv
where len(right(Str,len(Str) - charindex(',',Str))) > 0
)
select col from tbl_for_csv


4. Generate Fibonacci Series upto a given number

declare @maxval int
set @maxval = 1000;

with fibonacci as
(
select 0 as a ,1 as b
union all
select b,a+b from fibonacci where b<@maxval) select a as series from fibonacci


5. Get the fraction given a decimal

declare @decimal_input decimal(18,10)
set @decimal_input = 0.122

declare @mutiplier bigint
set @mutiplier = 100000;
with cte as
(select cast(@decimal_input*@mutiplier as bigint) as a,@mutiplier as b
union all
select b,a%b from cte where b!=0)
select cast(cast(@decimal_input*@mutiplier as int)/abs(a) as varchar) + '/' + cast(@Mutiplier/abs(a) as varchar) as fraction from cte
where b = 0


6. Get the binary value given an integer

declare @num int
set @num = 25;
with cte as
(select @num/2 as A, cast((@num%2) as varchar) as B
union all
select A/2, cast(cast((A%2) as varchar) + B as varchar)
from cte where A != 0
)
select @num as decimal, B as binary from cte where A = 0