SQL Server PIVOT

SQL Server relational database management system. It has core functions that create, manipulate, and store data very efficiently. SQL Server contains all these characteristics and it has an extremely user-friendly installation interface, unlike other database servers that require extensive command-line configurations.

In this article, we are going to learn about the PIVOT operator in SQL Server. We will learn how we can use the Pivot operator with simple data sets, dynamic columns, and as well as with aggregate functions like AVG() and SUM().

PIVOT is a very effective tool if we are performing data analysis on a large data set and want to aggregate the row-based data to fetch meaningful results from them.

Introduction to PIVOT Operator in SQL Server

The PIVOT operator in SQL server was introduced back in 2005. It is a powerful operator that is used to convert the rows of a table into columns. It is generally used to generate a summarized result set which makes it easy to analyze large datasets.

It is also used with aggregation functions like SUM(), AVG(), and COUNT() to summarize the data. For example, if you have multiple records of the same identity row then you can PIVOT the table to aggregate the results of the same row data into a single column.

We can also use the UNPIVOT operator to get the result set back in the normal form. Now that we have an understanding of the PIVOT operator let’s understand its syntax.

Working of Pivot Operator

Syntax:

SELECT <non-pivoted column>,  

    [pivoted column1], [pivoted column2], ..., 
 
FROM  
    (SELECT <non-pivoted column>,  
        [pivoted column],  
        <value column>  
    FROM <source table>  

    ) AS <alias for the source subquery>  
PIVOT  
(  
    <aggregate function>(<value column>)  

FOR  
[<pivoted column>] IN ( [pivoted column1], [pivoted column2], ..., [pivoted columnN] )  

) AS <alias for the pivot table>

Explanation of Syntax:

• <non-pivoted column>: Columns that will remain unchanged in the result set. Select the columns here on which you don’t want to perform the PIVOT opeartions.

• [pivoted column1], [pivoted column2], : Name of the Columns you want to be pivoted.

• <value column>: The data that will be aggregated.

• <aggregate function>: The name of the function is declared here which will used for aggregation (e.g., SUM, AVG, COUNT).

Examples on PIVOT Operator in SQL server

To understand the PIVOT Operator in SQL Server in Depth, we need a table on which we will perform some operations and queries. So here we have Examdata Table which Consist of Name, Subject and Marks as Columns. After Inserting some data into the Examdata Table, The table looks:

Table-ExamData

Example 1: Simple PIVOT

In this example we will learn How we can create a simple PIVOT table that will convert the row wise data into column wise data using the PIVOT operator.

Query:

SELECT * FROM (
  SELECT
  [id],
    [Name],
    [Subject],
    [Marks]
  FROM ExamData
) ExamResults
PIVOT (
  SUM([Marks])
  FOR [Subject]
  IN (
    [Operating Systems],
    [DSA],
    [DBMS],
    [Computer Networks]
  )
) AS PivotTable

Output:

Pivoted Table

Explanation: In the following example we have created a Pivot for the original table Examdata. All the row wise data is converted into the column wise form. Individual record of each student into a single row and the subjects of the student are converted into the columns of the table. The PIVOT operator aggregates the Marks column using the SUM function for each unique value in the Subject column. The FOR[Subject] clause specifies the columns that will be created in result set.

Example 2: PIVOT with Dynamic Columns

In this example, We will learn how we can create a procedure to implement dynamic columns in a PIVOT. So that if a new column is added in the table we don’t have to explicitly add the column name in the query to form the PIVOT.

Query:

CREATE PROCEDURE dbo.DynamicPivotTableInSql1
  @ColumnToPivot  NVARCHAR(255),
  @ListToPivot    NVARCHAR(255)

AS
BEGIN
 
  DECLARE @SqlStatement NVARCHAR(MAX)
  SET @SqlStatement = N'
    SELECT * FROM (
      SELECT
        [id],
        [Name],
        [Subject],
        [Marks]
      FROM Examdata
    ) ExamResults
    PIVOT (
      SUM([Marks])
      FOR ['+@ColumnToPivot+']
      IN (
        '+@ListToPivot+'
      )

    ) AS PivotTable ';
 
  EXEC(@SqlStatement)
 
END

EXEC dbo.DynamicPivotTableInSql1
  N'Subject'
  ,N'[Operating Systems],[DSA],[DBMS],[Computer Networks]

Output:

Output Before adding a new column

Explanation: In the following example, We have created a storedProcedure DynamicPivotTableSql1. The stored procedure takes two parameters @columnToPivot and @ListToPivot and dynamically creates a PIVOT. We have created this stored procedure so that we will not have to run long PIVOT queries to add new columns to our Pivot data. The above query returns the same result as the above example but it is executed dynamically.

Now we have modified our original table ExamData and added marks of the new subject Machine Learning.

Modified Table

Query for adding new column dynamically:

EXEC dbo.DynamicPivotTableInSql1
  N'Subject',N'[Operating Systems],[DSA],[DBMS],[Computer Networks],[Machine Learning]'

Output:

Output After adding a new column

Explanation: As the table data is modified now so the previous command will not include the new column Machine Learning in the result set. To simply include the new column in the PIVOT result set we can execute the above query and add the name of the newly added column [‘Machine Learning‘] in the last.

Example 3: PIVOT with Multiple Aggregations

In this example we will learn how we can perform multiple aggregation on a single PIVOT table. We will learn how we can aggregate multiple columns and form a single resultant value from them.

Query:

-- Select all the subjects of the student we want to show as columns
SELECT id, Name, [Operating Systems], [DSA], [DBMS], [Computer Networks], [Machine Learning]

-- Define the name of the column where the overall average of the student will be shown.
 AS OverallAverage  
FROM (
    SELECT id, Name, Subject, Marks
    FROM ExamData
) ExamResults

PIVOT (
    AVG(Marks)
    FOR Subject IN (
        [Operating Systems], [DSA], [DBMS], [Computer Networks], [Machine Learning]
    )
) AS OverallAveragePivotTable;

Output:

Output

Explanation: In this example the original table ExamData had rows for each student, subject and their marks. The following query performs multiple aggregations on the original data using the AVG and SUM function to create a PIVOT in which column for each subject is created along with a OverallAverage column which represents the overall average marks of the student including all the subjects.

Example 4: How to UNPIVOT

In this example, We will see how we can reverse the results of the PIVOT operator and convert the row wise data again into column wise form. Let us consider the following table:

Original Table-Studetns

Query:

SELECT id, Marks, ActualMarks 
FROM   
   (SELECT id, Marks1, Marks2, Marks3, Marks4, Marks5  
   FROM students) p  
UNPIVOT  
   (ActualMarks FOR Marks IN   
      (Marks1, Marks2, Marks3, Marks4, Marks5)  
)AS unstudents;  
GO

Output:

Unpivoted Table

Explanation: In the following example, We have performed the reverse of the PIVOT operator using the UNPIVOT operator. This query converts the columns Marks1, Marks2,Marks3,Marks4,Marks5, from the students table into rows creating new columns ActualMarks and repeating the same columns id and Marks from the original table, in the result set we get the rotated version of the original table students.

Conclusion

In this article we have learned about the use of PIVOT operator in SQL server. We learnt that we can use the PIVOT operator to convert the row data into column data which makes it easy for data analysis. We have also learnt how to use PIVOT with dynamic columns, with WHERE clause and with multiple aggregate functions. We hope this article has helped you to learn about the PIVOT operator. Do like the article if you have learnt something valuable. Happy Learning.