Pivot tables in sqlserver 2008
In this article we are going to see what is a PIVOT table. What are the advantages of using a pivot table.
A Pivot Table is a Table which can count and total the data stored in a column and use it to create a second table which displays the summarized data. The PIVOT keyword in sqlserver displays the values of a specified column as column name.
In this article we are going to see what is a PIVOT table. What are the advantages of using a pivot table.
A Pivot Table is a Table which can count and total the data stored in a column and use it to create a second table which displays the summarized data. The PIVOT keyword in sqlserver displays the values of a specified column as column name.
Let us see an example below:
1.First create a table student with follwing columns:StudentName,Subject,Marks as shown below:
2.Then insert some data into the table.
3.In the first example how to display average marks scored by each student in each subject. We will display the StudentName and each SubjectName(IT,MATHS) as columns.
SELECT *
FROM StudentDetails PIVOT (avg(Marks) FOR subject IN ([IT], [Maths])) AS Aggregate
In the above code the result will be StudentName and Subject Names(IT,Maths) as column names and average of the marks scored in each subject by each student as shown below:
4.In the second example we will display the maximum marks scored by each student in each subject as shown below:
SELECT *
FROM StudentDetails
PIVOT (max(Marks) FOR subject IN ([IT], [Maths])) as max
5.In the third example we will display student names as column names and calculate the maximum marks scored by each student in each subject.
SELECT *
FROM StudentDetails
PIVOT (max(Marks) FOR studentname in ([priya],[seshu])) max
Note:
1.When using Pivot keyword in sqlserver, make sure you dont specify the column names explicity in the select clause otherwise it will give an error:done use sql statement as below:
SELECT subject,marks
FROM StudentDetails
PIVOT (max(Marks) FOR studentname in ([priya],[seshu])) max
instead use select * from...
2.Make sure you give an alias after the sql statement (max in the above queries) otherwise it will give an error as incorrect syntax near ')'.
Follow