Pivot in SQL Server (Rows to Columns)

In this SQL Server, you will learn about Pivot in SQL Server, a very useful way to transform rows into columns.

You will be introduced to ‘What is Pivot in SQL Server?” which explains the meaning of Pivot.

Then, the syntax of how to pivot the table or convert the values of a column into a column header. After that, with an example, you will understand how to pivot the real-world tables.

Table of Contents

What is Pivot in SQL Server?

Pivot in SQL Server means turning the rows of the table into columns. Sometimes, for better data analysis and reporting, you need to perform aggregation on the data and then change the rows into a very understandable columnar format.

Overall, in simple words, presenting (transforming, changing, converting) the rows of a table into columns is called Pivoting in SQL Server.

But how can you pivot the table? For that, SQL Server provides an operator called PIVOT.

The syntax is given below.

SELECT column_1, column_2, ..  

FROM  
   table_name  
PIVOT  
(  
    AGGREGATE_FUNCTION(column_to_pivot)
FOR   
[pivot_columns  
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>

Where,

column_1, column_2, ..: These are the columns you should include in the final result set.
table_name: It is the table name from which you want to pivot the data or rows.
AGGREGATE_FUNCTION(column_to_pivot: This is the aggregate function like MIN, MAX, SUM, and AVG. The column_to_pivot is column data whose values are transformed into columns.
pivot_column: This is the column based on which the transformation is done.
first pivoted column, second pivoted column, and last pivoted column: The set of values from the pivoted columns becomes the column header in the output.
alisa for the pivot table: It is a different name or alias for the pivoted table.

Let’s see an example of how to change the table rows into columns.

For example, you have an EmployeeSales table, as shown below.

So here you have a task to change the Month values into columns so that you can compare the sales amounts across different months for each employee. For that, use the query below.

SELECT EmployeeID, [January], [February], [March]
FROM
(
	SELECT EmployeeID, Month, SalesAmount
	FROM EmployeeSales
) AS SourceTable
PIVOT
(
	SUM(SalesAmount)
	FOR Month IN ([January], [February], [March])
) AS PivotTable;

From the output of the above query, the result set contains the total sales amount for each employee in each month. For example, EmployeeID, with 1, made 1000, 1100, and 1050 sales in January, February, and March, respectively.

Also, if an employee doesn’t have monthly sales, it shows the NULL value. For example, look at EmployeeID with 2 in March month.

But here, you can see the value of the month column is now the table header after performing the pivot operation. Look at the table’s columns in a result set, which is the value of the Month column.

Let’s understand the query part one by one first SELECT EmployeeID, [January], [February], [March]. This means including these column headers in the final result. You can also see it in the output of the above query.

Then SELECT EmployeeID, Month, SalesAmount FROM EmployeeSales; this is the inner query, which means retrieving all the data from the EmployeeSales table that we want to pivot.
After that, the clause PIVOT is used to transform the values into columns, here SUM(SalesAmount), which means compute the sum of all the values in the SalesAmount column.
Lastly, FOR Month IN ([January], [February], [March]), this means pivoting the Month column and transforming the values of the Month column into new columns such as January, February, and March.

Let me explain the above query again, but this time with the sequence of execution of the query; if it is unclear, this will clear your concepts about pivoting tables in SQL Server.

First, the FROM clause gets all the data from the EmployeeSales table through an inner query: SELECT EmployeeID, Month, SalesAmount FROM EmployeeSales.

Remember, here we have the outer query and the inner query.

After fetching data from the EmployeeSales table, the PIVOT operation is performed on the fetched data. In this operation, the value of the Month column is converted into new column headers (January, February, March).
- Within PIVOT(), the SUM(SalesAmount) function aggregates the sale amount for each EmployeeID under these new columns.
After the completion of the PIVOT operation, the outer query, which SELECT EmployeeID, [January], [February], [March] is processed,
- This outer query decides which columns to include in the result set. As you can see in this outer query, four columns are specified: EmployeeID, January, February, and Month.

You now understand how to create a SQL Server pivot table from the above.

Pivot Table in SQL Server using Dynamic Stored Procedure

You can create the pivot table using the stored procedure; here, you need to put all the code related to the table within the stored procedure and then customize some parameters to generate the pivot table based on that parameter.

So, here, I will take the query we used in the above section, which is about converting month values into column headers.

So, create a store as shown below.

CREATE PROCEDURE PivotTable
  @ColumnForPivoting  NVARCHAR(255),
  @ListForPivoting    NVARCHAR(255)
AS
BEGIN
 
  DECLARE @SqlStmt NVARCHAR(MAX)
  SET @SqlStmt  = N'
  SELECT EmployeeID, [January], [February], [March]
FROM
(
	SELECT EmployeeID, Month, SalesAmount
	FROM EmployeeSales
) AS SourceTable
PIVOT
(
	SUM(SalesAmount)
	FOR ['+ @ColumnForPivoting +'] IN ('+ @ListForPivoting +')
) AS PivotTable';

  EXEC(@SqlStmt )
END

After executing the above query, the stored procedure PivotTable is created. Now use the query below to call the store procedure with the parameters column to pivot, which is ‘Month’ and List to pivot, which is [January], [February], [March].

EXEC PivotTable
  N'Month'
  ,N'[January], [February], [March]';

Pivot Table in SQL Server using Dynamic Stored Procedure

The result contains the Month values as the column header; this pivot table is generated using the PivotTable Stored procedure.

To learn more about stored procedures, visit the SQL Server Stored Procedure Tutorial.

In the stored procedure, only the column name whose value you want to make as the column header and the list of values you want to pivot.

This is how to use the stored procedure to create a Pivot table in SQL Server.

Conclusion

In this SQL Server tutorial, you learned how to transform the column values into column headers using the PIVOT operator. You have converted the Month column value into a column header.

Also, you have learned how to dynamically use the stored procedure to create a pivot table.