Mehedi's Earth

Today, I am going to explain how to make PIVOT table in SQL Server. Pivot is a table which transposes rows into columns. It helps users to convert rows into columns from a SQL Server table and creates customize reports. This is a very essential technique for data analysis.

Let's prepare the sample data:

IF OBJECT_ID('Students', 'U') IS NOT NULL
    DROP TABLE Students;
GO

CREATE TABLE Students(
[name] varchar(20)
,[subject] varchar(20)
,[marks] int
)

INSERT INTO Students([name], [marks], [subject])
VALUES('Bob', 95, 'English')
,('Bob', 56, 'Math')
,('Bob', 89, 'Physics')
,('Bob', 85, 'Chemistry')
,('Joe', 90, 'English')
,('Joe', 80, 'Math')
,('Joe', 85, 'Physics')
,('Joe', 92, 'Chemistry')
,('Jack', 100, 'English')
,('Jack', 92, 'Math')
,('Jack', 75, 'Physics')
,('Jack', 83, 'Chemistry')

Select * from Students;

Sample Data Output

Our sample data is ready. Now we will make it to a PIVOT table following below steps: 

• Creating a temporary result using a derived table
• Applying PIVOT operator

SELECT * FROM (
    SELECT name
    ,marks
    ,subject
    FROM Students
) A
PIVOT (
    SUM(marks)
    FOR subject IN(
    [English]
    ,[Math]
    ,[Physics]
    ,[Chemistry]
    )
) AS B

PIVOT Table Output

If we examine the script, we can see two separate parts

1. A derived table where we select the sample data
2. PIVOT - which transpose the data. PIVOT part has some operators/keywords like
   
   • SUM Operator - Here SUM operator is used to aggregate the values of the marks column so that it can be used in the pivot table. One important note, it is mandatory to use an aggregated column in PIVOT.
   • FOR Keyword - It is used to identify the values which is to be converted from rows into columns. 
   • IN Keyword - It lists all the distinct values from the pivot column that we want to convert to columns

There is a limitations of PIVOT. We need to hardcode values for the columns i.e., if a new subject value is inserted into the table, the new value will not be displayed as a column in PIVOT table as it is not defined in the IN operator list. So, we need to add new value in IN list if we want to display it.

Hope this is helpful to you. Happy TSQLing!