### 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)

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

(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';

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,

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

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;

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

(

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

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

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

## 2 comments:

hi!

i have a table named "Unit"

which has to main columns

unitid and parentunitid.

for a given search depth and

rootunit ,i need to retreive all units under this root

i tried many variations like:

WITH UnitsCTE(UnitId,level) AS

(

-- Anchor member definition

SELECT u.UnitId ,1 as level

FROM qf.Unit u

WHERE u.UnitId = @RootUnitId

UNION ALL

-- Recursive member definition

SELECT u.UnitId,level + 1

FROM qf.Unit u

JOIN UnitsCTE cte ON u.UnitId = cte.UnitId

WHERE u.ParentUnitId = @RootUnitId and level < @depth

)

SELECT *

FROM UnitsCTE

and got nothing

do anyone has an idea?

thanks in advance

wow.....great...!

It's very helpful!

good job.

Regards,

Biju.K.S

## Post a Comment