Introduction

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

• Output

• 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

• Output

As you can see in the figure above, the pivot table has been created and we have converted the rows into distinct columns.

Conclusion

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