Computed Column in SQL Server

How do you create Computed/Calculated column in SQL Server?

A computed column is computed based on an expression that can use other columns of the same table. The expression can be a non-computed column name, constant, function, and any combination of these connected by one or more operators. However, the expression cannot be a sub-query.
 
Scenario: I want to create a computed column which should be calculated based on the values of other columns. For instance, Employee table has DateOfBirth column and I want to create a computed column to store the Age of an employee (in years). Another example could be to display the Grade of a student based on the marks obtained by the student.

Example 1: Employee (EmployeeID, EmployeeName, BasicSalary, DOB)

EmployeeID	EmployeeName	BasicSalary	DOB
1	Hari	5000	1983-05-05
2	John	3500	1978-10-25
3	Peter	4500	1982-02-10
4	Ravi	2500	1980-03-04
5	Max	3600	1985-09-20

Problem: Add a new computed column Age in Employee table.

Example 2: Student (StudentID, StudentName, MarksObtained)

EmployeeID	EmployeeName	MarksObtained
1	Hari	74
2	John	80
3	Peter	55
4	Ravi	34
5	Max	67

Problem: Add a new computed column Grade in Student table using following grading system:

If MarksObtained >= 75 then A
If MarksObtained >= 60 then B
If MarksObtained >= 40 then C
Else F
 

CONCATENATE Column values into Rows

How do you CONCATENATE column values into rows?

Scenario: There is a table Student (ID (identity), StudentName, SubjectName). You need to concatenate all the subject of each student and display along with student name. Below is sample of input data:

ID	StudentName	SubjectName
1	Hari	Science
2	Tony	Biology
3	Hari	Maths
4	Ravi	Physics
5	Ravi	Chemistry
6	Ravi	Botany

 

Here is the expected output:

StudentName	SubjectName
Hari	Science,Maths
Ravi	Physics,Chemistry,Botany
Tony	Biology

 

Here is the solution: