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.

Derived Columns with CTEs

Derived Columns with CTEs.


Let's say we have a simple query selecting few columns from some table. Now we are asked do some derived columns from this queries. However, some of these derived columns
are based another derived columns.

One way to do this is to create a view and get a first set of derived columns and than create another view on the first view to derive another sets of columns.

However there is another approach to do this. Take the initial query results and put into a common table expression, use another common table expression to get a first derived columns and
use it derive next set of columns. Theoretically this can be done n-times but to illustrate this, let's try to limit this to 3 CTEs.



This is simple table I have created to demonstrate derived columns from CTEs. Not a perfect but just to illustrate the concept.



Create table Student_Grade
(ID int IDENTITY,
FirstName varchar(50),
MiddleName varchar(10),
LastName varchar(50), 
Maths float,
Biology float,
Chemistry float,
Physics float,
English float,
ForeignLanguage float,
Physical_Education float
)

--Let's enter some data in our sample tables.

Insert into Student_Grade
VALUES
('Abby', 'M', 'Smith',40,67,38,25,67,71,34),
('Bbby', 'M', 'Smith',50,77,48,35,57,72,44),
('Cbby', 'M', 'Smith',68,87,58,45,67,73,54),
('Dbby', 'M', 'Smith',78,97,68,55,27,74,64),
('Ebby', 'M', 'Smith',88,57,78,65,17,75,74)

--Let's see if we have our data in the table or not.

Select * from Student_Grade;



-- Let's say we are asked to find what student made in total_science_score, combined average in Science subjects (Biology, physics, and Chemistry) and English and ForeignLanguage.

With FirstSelect AS
(
SELECT  [ID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
      ,[Maths]
      ,[Biology]
      ,[Chemistry]
      ,[Physics]
      ,[English]
      ,[ForeignLanguage]
      ,[Physical_Education]


  FROM [dbo].[Student_Grade]
  ),

  --Here for simple purpose I am deriving two columns -Total_Science_Score and Total_language_Score
  SecondSelect AS
  ( Select [ID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
 ,[Maths]
      ,[Biology]
      ,[Chemistry]
      ,[Physics]
      ,[English]
      ,[ForeignLanguage]
      ,[Physical_Education]
 ,[Biology]+[Chemistry]+ [Physics] As Total_Science_Score
  ,[English]+[ForeignLanguage] As Total_language_Score

 From FirstSelect
 ),

--Here I am deriving another columns which was derived in previous statement

ThirdSelect AS
(
 Select
 [ID]
      ,[FirstName]
      ,[MiddleName]
      ,[LastName]
 ,[Maths]
      ,[Biology]
      ,[Chemistry]
      ,[Physics]
      ,[English]
      ,[ForeignLanguage]
      ,[Physical_Education]
 ,Total_Science_Score
 ,Total_language_Score
 , Total_Science_Score + Total_language_Score AS Total_Science_Language_Score
  from SecondSelect
 )

 Select * from ThirdSelect