Thursday, November 15, 2012

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
 

Friday, November 2, 2012

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: