In this article, we are going to learn how to use Pivot Table in SQL.
Pivot and Unpivot are relational operators in SQL, that are used to transform one table into another in order to achieve more simpler view of table. Pivot Table is used whenwe want to transfer data from row to column. It help us to create interactive tables that quickly compares and combines large amount of data.
Create StudentGrades Table
CREATE TABLE StudentGrades ( [Student] VARCHAR(50), [Subject] VARCHAR(50), [Marks] INT )
Insert Record To StudentGrades Table
INSERT INTO StudentGrades VALUES ('John', 'Mathematics', 100), ('John', 'Science', 95), ('John', 'Geography', 90), ('Amit', 'Mathematics', 90), ('Amit', 'Science', 90), ('Amit', 'Geography', 100)
Before Applying Pivot Operator
SELECT * FROM StudentGrades
After Applying The Pivot Operator
SELECT * FROM ( SELECT [Student], [Subject], [Marks] FROM StudentGrades ) StudentResults PIVOT ( SUM([Marks]) FOR [Subject] IN ( [Mathematics], [Science], [Geography] ) ) AS PivotTable
As you can see in the figure above, the pivot table has been created and we have converted the rows into distinct columns.
In this article, I have explained what a pivot table in SQL and how to create one. I have also demonstrated a simple scenario in which I implement and use pivot table.
Also, Check How to Use Trigger In SQL