SQL Server: Converting Integer to Binary data

Let's first a create table which will be use to demonstrate conversion of integer to binary values. To keep this thing simple, let's keep our table as small as possible.

Create table NumberTable
(NumberID int IDENTITY Primary Key NOT NULL
,Number int
,Number_Name varchar(100)
)

--Let's insert some data in this table
Insert into  NumberTable
 VALUES (1, 'One'), (2, 'Two'), (3, 'Three'), (4, 'Four'), (5, 'Five')

-- Let's see what we have in this table.
Select * from Number;



--Let's convert this number to binary value. To do so, we will use Common Table Expression

With BinaryConversation AS
(Select Number, Number as WorkingNumber, cast('' as varchar(max)) as binary_values from NumberTable
-- Now we will do Union ALL with converted values from Common table Expression
UNION ALL
Select B.Number, B.WorkingNumber/2, cast(B.WorkingNumber%2 as varchar(max))+B.binary_values
FROM BinaryConversation B
Where WorkingNumber > 0) --This condition keep our results set to only converted values)
Select Number, binary_values from BinaryConversation Where WorkingNumber = 0
Order BY Number
;

Thanks to all those bloggers from whom I learn.

Here's a good resource I found on Binary and Decimal.