Jun 29, 2006

Interesting queries using recursive Common Table Expressions



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


Anonymous said...

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

and got nothing
do anyone has an idea?
thanks in advance

Anonymous said...

It's very helpful!
good job.


Post a Comment