Simple recursive CTE

I like CTEs. Mostly because it lets me have a derived table that I can refer to multiple times. I don't often use the recursive features of it. But when I do, I repeatedly feel amazed at the power available.

Today I had a challenge to produce a nice SQL way of converting integers to binary. 

So I pulled out the CTE. 🙂

with ctebins as
(select num as num_orig, num as working_level, cast('' as varchar(max)) as binval
from nums_bin
union all
select c.num_orig, c.working_level / 2, cast(c.working_level % 2 as varchar(max)) + c.binval
from ctebins c
where c.working_level > 0
)
select num_orig, binval
from ctebins
where working_level = 0
;

What this is doing is to populate the top level of the CTE with a bunch of numbers from nums_bin, along with a blank string. I know I don't have to use varchar(max) – it's not going to get that big after all…

Then it fills the CTE with rows using recursion. I keep the original number (to use later), and keep cutting it in half to get the binary number out. Then I query the full CTE for the last level of recursion – when my working number got down to zero.

It works nicely, and it's very fast on a big set of numbers.